sql - Include 0 (zero) in Count query without joining other tables (if possible)-CodePudding

I am trying to do a count of a single table but include 0 in the count. However I am struggling to do this, and all the google searches have used joins.

This is my table.

Table name: CustomerLog

Code   Submitted            Type              CompletedState
----   -------------------  ----------------  --------------
1099   2022-10-08 05:45:01  Update_Activity1  Failed
1099   2022-10-08 06:45:01  Update_Activity2  Failed

This is my code:

select code,count(code) as Successes
from CustomerLog
where CompletedState = 'Succeeded'
group by Code

However 1099 doesn't appear in the results. I need the count to show as 0, like below:

Code  Successes
---- ----------
1099  0

Can anyone assist with this please?

CodePudding user response：

You can do conditional aggregation. For example:

select
  code,
  count(case when completedstate = 'Succeeded' then 1 end) as successes
from customerlog
group by code