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

问题描述

大家好.这是我的代码:

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)))

我面临的问题是，如果没有值，Count 函数不返回 0，如果没有结果值，Sum 函数不返回 NULL取回.相反，它只是输出空白.为什么会这样，我该如何解决?

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 outputs blank. Why is that so and how can I fix it?

这是示例输出的屏幕截图.

Here is a screenshot of sample output.

这当然不是我想要的.我希望它输出零和空.请帮帮我，我不知道怎么回事.谢谢.

Of course this is not what I want. I want it to output zero and null. Please help me, I do not know what's wrong. Thank you.

推荐答案

使用 GROUP BY 子句时，当源中不存在记录时，您不能期望输出任何记录.

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

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

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

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

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

例如:

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

将返回一个值为0"的记录，其中:

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

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

不会返回任何记录.

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