Including Zero Count in a SQL GroupBy Query-CodePudding

I've two tables and i want to get the monthly ingested data in my application by combining this table and that count should include Zero also. If no data ingested in a application then that too should appear in the resultset. But using the below query i'm not able to include the Zero count. Not sure what i'm doing wrong here.

`select count(*) as Total_Count, appprofile as Application
from Reference r
LEFT OUTER JOIN docapplication d ON 
d.did = r.did where
d.appprofile in ('EbsApp_0','EbsApp_2','EbsApp_3','EbsApp_4','EbsApp_5','EbsApp_6','EbsApp_8','EbsApp_9','EbsApp_10','EbsApp_11','EbsApp_12','EBS_DOC1','EBS_DOC2')
and
r.CREATEDATE >= '1-Jul-21 12.00.00.000000000 AM' and 
r.CREATEDATE <= '31-JUL-21 11.59.00.000000000 PM'
group by d.appprofile
order by d.appprofile;`

Result:

CodePudding user response：

It looks like you want to count references per application. So select from application and outer join references, not vice versa. Use a non-nullable reference column for counting. It will be null in outer-joined rows and thus not counted, giving you a result of zero if an application does not have a reference.

Be aware that conditions on the outer-joined table belong in the ON clause. If an application has no reference match, the reference columns will be null in the outer-joined row. If you put a condition in the WHERE clause (such as where outer_joined.column > 1) you dismiss the outer joined row and end up with a mere inner join.

I've also amended your date comparision. Never compare dates and strings. Use date literals or timestamp literals.

select
  count(r.did) as total_count,
  d.appprofile as application
from docapplication d
left outer join reference r on r.did = d.did
                           and r.createdate >= date '2021-07-01'
                           and r.createdate < date '2021-08-01'
where d.appprofile in ('EbsApp_0', 'EbsApp_2', 'EbsApp_3', 'EbsApp_4', 'EbsApp_5',
                       'EbsApp_6', 'EbsApp_8', 'EbsApp_9', 'EbsApp_10', 'EbsApp_11',
                       'EbsApp_12', 'EBS_DOC1', 'EBS_DOC2')
group by d.appprofile
order by d.appprofile;