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!
SELECT IsNull(Count(InvNo),0) AS RecordCount, BatchID
FROM BatchOrders
GROUP BY BatchID
March 25, 2021 at 4:49 pm
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
March 25, 2021 at 4:52 pm
Declaring db owner dbo. - no joy. Yes, the table is empty. That's why I want to get result of 0 zero.
March 25, 2021 at 5:05 pm
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
March 25, 2021 at 5:10 pm
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
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
March 25, 2021 at 5:12 pm
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 25, 2021 at 5:17 pm
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
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.
SELECT Count(InvNo) AS RecordCount, BatchID
FROM BatchOrders
GROUP BY BatchID
UNION ALL
SELECT 0, NULL
March 25, 2021 at 7:04 pm
What about checking @@rowcount? This way 0 would only show up if there are no rows
SELECT Count(InvNo) AS RecordCount, BatchID
FROM BatchOrders
GROUP BY BatchID;
if @@ROWCOUNT=0
SELECT 0, NULL;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
March 25, 2021 at 7:37 pm
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)
SELECT Count(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
March 25, 2021 at 7:54 pm
Nicely done The Dixie Flatline 🙂
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
March 25, 2021 at 8:18 pm
Yes, this is better. Thank you all.
March 25, 2021 at 8:28 pm
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply