Search code examples
sqlt-sqlcountsql-server-2008-r2sum

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

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.

A

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.

Solution

  • 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 has 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.