Count Null Records with Group BY

Question

Post reply

Count Null Records with Group BY

shank-130731

SSC Eights!

Points: 890
More actions
March 25, 2021 at 4:41 pm
Go to Answer
#3862076
I've searched and tried many variations, but must be missing something simple. If there are no records, I'm trying to get RecordCount to display zero '0'. I don't get errors, just no results. What am I missing?

Thanks!
```
SELECTIsNull(Count(InvNo),0) AS RecordCount, BatchID
FROMBatchOrders
GROUP BY BatchID
```
shank-130731

SSC Eights!

Points: 890
More actions
March 25, 2021 at 5:25 pm
Answer
#3862102
Thank you all for input. This one got me what I needed. I apologize for not being clear. I was expecting a zero count if there were no rows. And now I see the difference in counting a value vs rows.
```
SELECTCount(InvNo) AS RecordCount, BatchID
FROMBatchOrders
GROUP BY BatchID
UNION ALL
SELECT 0, NULL
```

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101095 More actions · Answer 1

Seems like the table must be empty.

Make sure you are in the correct db.

A long shot, but you should always put the schema name on the table anyway, for performance generally but there in case there might (accidentally?) be another table named BatchOrders but under a different schema:

FROM dbo.BatchOrders

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

shank-130731 SSC Eights! Points: 890 More actions · Answer 2

Declaring db owner dbo. - no joy. Yes, the table is empty. That's why I want to get result of 0 zero.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

There is a difference between returning a null value and returning no rows. What would the batchID be if there are no rows for a given batchID?

If you have another table that lists all the potential batchIDs, you can do this.

declare @BatchOrders table (invno int, batchid int)
insert into @batchorders (batchID, invno)
values (1,1234),(1,1983),(1,2022),(2,1540),(2,1781),(3,2121)

declare @BatchMaster table (batchid int)
insert into @BatchMaster values (1),(2),(3),(4)


select bm.BatchID, count(InvNo) as RecordCount
from @BatchMaster bm
left join @BatchOrders bo on bo.batchid = bm.batchid
group by bm.batchID

As you will see, knowing that there is a batch #4 enables counting zero rows.

One final thing, if you have any rows that have a BatchID but a NULL for InvNo, they will not be reflected in the RecordCount column. If that is not the desired behavior, switch to count(*) instead of count(InvNo).

(By the way, tables don't have records they have rows. 😉

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Steve Collins SSCrazy Points: 2157 More actions · Answer 4

COUNT doesn't return 0 because you've also requested BatchID in the SELECT list. The FROM clause is evaluated first and there are no rows. On its own COUNT will always return an integer. If you got rid of BatchID from the SELECT you could also remove the ISNULL function and COUNT would return 0 or 1, 2, ... [Edit] the GROUP BY would also need to be removed

This reply was modified 3 years, 8 months ago by Steve Collins.

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Phil Parkin SSC Guru Points: 246960 More actions · Answer 5

Maybe you could add

UNION ALL
SELECT 0
      ,NULL;

to the end of your query. Then a (0,NULL) row will always appear in your results.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 6

Wouldn't it be easier just to take the difference between the count of a column that will never be NULL (like the PK column) and the column in question?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Collins SSCrazy Points: 2157 More actions · Answer 7

What about checking @@rowcount? This way 0 would only show up if there are no rows

SELECTCount(InvNo) AS RecordCount, BatchID
FROMBatchOrders
GROUP BY BatchID;
if @@ROWCOUNT=0
    SELECT 0, NULL;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 8

That would produce TWO result sets, Steve. The second one would not have column names. Plus you can't really union the two queries if you are relying on @@ROWCOUNT.

Here is a variation on Phil's idea. It should only return the NULL, 0 row if there are no rows in the BatchOrders table.

declare @BatchOrders table (BatchID int, InvNo int)

SELECTCount(InvNo) AS RecordCount, BatchID
FROM@BatchOrders
GROUP BY BatchID

union all

select 0, null
where not exists (select 1 from @BatchOrders)

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Steve Collins SSCrazy Points: 2157 More actions · Answer 9

Nicely done The Dixie Flatline 🙂

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

shank-130731 SSC Eights! Points: 890 More actions · Answer 10

shank-130731

SSC Eights!

Points: 890

March 25, 2021 at 8:18 pm

#3862169

Yes, this is better. Thank you all.

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 11

The title of this thread threw me... I thought we were trying to count the NULLs in a given column... which is different than the original script, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)