Count Returning blank instead of 0

Question

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.

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.

What value would you expect to appear in the `Month` column - does SQL Server just have to magically make up a value for there? — Damien_The_Unbeliever, Oct 07 '13 at 09:59
But when there are *no* rows for it to process - the condition under which the result you want is 0 - what is it going to compute it from? — Damien_The_Unbeliever, Oct 07 '13 at 10:05
ohhh i see now i understand :) thanks @Damien_The_Unbeliever you helped me againd this time :) — Albert Laure, Oct 07 '13 at 10:06
this article may help; it talks about adding a left or right join to force the zero to be present. [link](https://learnsql.com/blog/zero-as-result-of-count-in-sql/) — jbobbins, Jan 31 '23 at 16:46

Dan · Accepted Answer · 2021-11-03T12:48:00.953

36

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.

edited Nov 03 '21 at 12:48

answered Oct 07 '13 at 09:59

Dan

10,480
23
49

the reason why i used group by is because of this error `Msg 8120, Level 16, State 1, Line 2 Column '#AllExpired.fld_LoanDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.` any suggestion ? how to remove the error with out using group by? – Albert Laure Oct 07 '13 at 10:00
Well, you can't remove the GROUP BY clause, if you want a list of the total count, grams and principal for each loan date month. An ugly workaround, if you want your original query to return a row with 0's, when no records are present, is to add something like this to your query: `UNION SELECT NULL AS [Month], 0 AS [COUNT], 0 AS [GRAMS], 0 AS [PRINCIPAL] WHERE (SELECT COUNT(*) FROM #AllExpired) = 0`, but a better solution would be to have your application handle the original query not returning any rows. – Dan Oct 07 '13 at 10:31
1

There are other ways around this problem, you can also use `COALESCE` with each field to return 0 when the result is `NULL`. Or use a correlated subquery to ensure the "top level" data is always returned any any aggregate functions will then return 0. – Tony Oct 07 '13 at 10:55
@Tony: COALESCE won't work, since there's no record to begin with. Your other suggested approach, using "top level" data and perhaps LEFT JOINs is much more elegant than my solution. – Dan Oct 07 '13 at 11:02
@Dan - True, `COALESCE` is only useful to return zero when the aggregate field can be `NULL`. The correlated sub query is a good solution but only if it does not cause performance issues, it can be slow on large data sets. – Tony Oct 07 '13 at 11:11

score 4 · Answer 2 · answered Oct 07 '13 at 09:53

I would imagine you need to change your joins from INNER to OUTER to ensure rows are returned even when there is no corresponding record in tbl_PawnItem -

SELECT 
    'Expired Item -'+ DateName(mm,DATEADD(MM,4,AE.fld_LoanDate)) as [Month]
    ,COUNT(PIT.fld_PawnItemID)'COUNT'
    ,SUM (PIT.fld_KaratGram)'GRAMS'
    ,SUM (PH.fld_PrincipalAmt)'PRINCIPAL'
FROM  #AllExpired AE
    LEFT JOIN Transactions.tbl_PawnItem PIT
        ON AE.fld_PawnMainID=PIT.fld_PawnMainID
    LEFT JOIN Transactions.tbl_PawnHisto PH
        ON AE.fld_PawnMainID=PH.fld_PawnMainID
GROUP BY DATENAME(MM,(DATEADD(MM,4,AE.fld_LoanDate)))

score 1 · Answer 3 · answered Oct 07 '13 at 09:52

1

Perhaps #AllExpired is empty, or one of the joins returns no results?

Remember inner joins need results on both sides in order to return, so because #AllExpired is empty the join returns nothing.

Change it to an OUTER join.

answered Oct 07 '13 at 09:52

m.edmondson

30,382
27
123
206

yes it is empty, well? isnt it supposed to output zero? when i use the function count>? – Albert Laure Oct 07 '13 at 09:54

Count Returning blank instead of 0

3 Answers3

Linked