CODEWITHSUNDEEP
sqlsql-servert-sqlgroup-by
ccuga
ccuga

Reputation: 3

Show 0 when count(*) query from one table

I'm running a query on one table to get a count of all the different errors that the data highlights. Most days there should be 0 errors. As a result, no records will appear in the result by the groupings. What I want is a record for each grouping to appear but to show a 0 in the count(*) column if there aren't any errors. How can I do this?

select ITEM_CODE, NBR, CATEGORY, count(*) as ERR_CNT
from ITEM_TRCKR
where (
       DATEDIFF(dd,[ITM_SENT_DT],GETDATE()) > 1
         AND [DSBRSMNT_RECVD_DT] = 'NULL'
   )
group by
ITEM_CODE, NBR, CATEGORY

Upvotes: 0

Views: 61

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

If you want all groupings, use a cross join to generate them then a left join to bring in the additional information:

select i.ITEM_CODE, n.NBR, c.CATEGORY,
       count(it.item_code) as ERR_CNT
from (select distinct item_code from ITEM_TRCKR) i cross join
     (select distinct nbr from item_trkr) n cross join
     (select distinct category from item_trkr) c left join
     item_trkr it
     on it.item_code = i.item_code and
        it.nbr = n.nbr and
        it.category = c.cateogry and
        DATEDIFF(day, it.ITM_SENT_DT, GETDATE()) > 1 and
        it.DSBRSMNT_RECVD_DT is null
group by i.ITEM_CODE, n.NBR, c.CATEGORY;

I assume that it.DSBRSMNT_RECVD_DT is a date. Hence the NULL comparison should be to the SQL concept NULL, not to the string 'NULL'.

Upvotes: 3

GMB
GMB

Reputation: 222462

Assuming that you have at least one record per day (with or without an error), one option is to do conditional aggregation:

select 
    item_code, 
    nbr, 
    category, 
    sum(case when dsbrsmnt_recvd_dt] = 'null' then 1 else 0 end) as err_cnt
from item_trckr
where [itm_sent_dt] < cast(getdate() as date) 
group by item_code, nbr, category

Note that <expr> = 'null' actually checks the exr against litteral string 'null'. If what you want is count the null values in the column, you can leverage the fact that count(<expr>) ignore nulls:

select 
    item_code, 
    nbr, 
    category, 
    count(*) - count(dsbrsmnt_recvd_dt) err_cnt
from item_trckr
where [itm_sent_dt] < cast(getdate() as date) 
group by item_code, nbr, category

Upvotes: 1

Related Questions