计数返回空白，而不是0 [英] Count Returning blank instead of 0

问题描述

  SELECT 
'已过期的项目 - '+ DateName（mm，DATEADD （MM，4，AE.fld_LOAN））作为[Month] 
，COUNT（PIT.fld_ID）'COUNT'
，SUM（PIT.fld_GRAM）'GRAMS'
，SUM .fld_AMNT）'PRINCIPAL'
 FROM #AllExpired AE 
 INNER JOIN Transactions.tbl_ITEM PIT 
 ON AE.fld_MAINID = PIT.fld_MAINID 
 INNER JOIN Transactions.tbl_HISTO PH 
 ON AE.fld_MAINID = PH.fld_MAINID 
 GROUP BY DATENAME（MM，（DATEADD（MM，4，AE.fld_LOAN）））
  

我面临的问题是，如果 Count 函数没有返回值， Sum 函数不返回NULL如果没有检索到结果值，而是它只是输出为空，为什么会这样？

https://i.stack.imgur.com/P372V.pngalt =A>

ofcourse这不是我想要它输出零和nulls。请帮我我不知道什么错了。谢谢。

解决方案

当您的来源中没有记录时，不能指望使用GROUP BY子句时输出任何记录。

如果您希望SUM和COUNT函数的输出为0，则不应使用GROUP BY。

原因是当您没有记录时，GROUP BY子句没有分组依据，然后无法给您任何输出。

示例：

  SELECT COUNT（*）FROM（SELECT'Dummy'AS [Dummy] WHERE 1 = 0）DummyTable 
 

将返回值为0的记录，其中为：

  SELECT COUNT（*）FROM（SELECT'Dummy'AS [Dummy] WHERE 1 = 0）DummyTable 
 GROUP BY [Dummy] 
  

将不会返回任何记录。

Good day everyone here is my code

SELECT 
    'Expired Item -'+ DateName(mm,DATEADD(MM,4,AE.fld_LOAN)) as [Month]
    ,COUNT(PIT.fld_ID)'COUNT'
    ,SUM (PIT.fld_GRAM)'GRAMS'
    ,SUM (PH.fld_AMNT)'PRINCIPAL'
FROM  #AllExpired AE
    INNER JOIN Transactions.tbl_ITEM PIT
    ON AE.fld_MAINID=PIT.fld_MAINID
    INNER JOIN Transactions.tbl_HISTO PH
    ON AE.fld_MAINID =PH.fld_MAINID
GROUP BY DATENAME(MM,(DATEADD(MM,4,AE.fld_LOAN)))

the problem I'm facing is that my Count function does not return 0 if it has no values, Sum function does not return NULL if there are no resulting values retrieved, instead it just output blank, why is that so? and how can i fix it?

here is a screen shot of sample output

ofcourse this is not i want i want it to output zero and nulls. please help me i do not know whats wrong. thank you.

解决方案

You cannot expect any records to be outputted when using a GROUP BY clause, when no records exist in your source.

If you want an output of 0 from the SUM and COUNT functions, then you should not use GROUP BY.

The reason is that when you have no records, the GROUP BY clause have nothing to group by, and then is not able to give you any output.

For example:

SELECT COUNT(*) FROM (SELECT 'Dummy' AS [Dummy] WHERE 1 = 0) DummyTable

will return one record with the value '0', where as:

SELECT COUNT(*) FROM (SELECT 'Dummy' AS [Dummy] WHERE 1 = 0) DummyTable
GROUP BY [Dummy]

will return no records.

这篇关于计数返回空白，而不是0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！