[SOLVED] How to generate a count of 0 in a group by with multiple columns with count(*)

How to generate a count of 0 in a group by with multiple columns with count(*)

I am desperately trying to figure out how to do something that is probably simply.

When I run this query, the output only counts records where there is a value greater than 0.

select year, code, type, count(*) as count
from  #senatetest2
group by year, code, type
order by year, code, type

It will give me:

Year  Code   Type   Count
2020   A      Z      1
2020   B      Y      2

When I want:

Year  Code   Type   Count
2020   A      Z      1
2020   B      Y      2 
2020   C      X      0

All case when queries use a column, however, I am not counting a column, I am counting all (*). Is there any way to get the count column to count and return an output of zero?

I have to do this by tomorrow for work (it's my day off) and I cannot get figured out.

Thank you so much in advance.

Solution

It can't make up data that doesn't exists, therefore you would need a driver table that contains the combination of Year|Code|Type that you would want in your output.

Something like:

CREATE TABLE driver (year int, code char(1), type char(1));
INSERT INTO DRIVER VALUES (2020,'A','Z');
INSERT INTO DRIVER VALUES (2020,'B','Y');
INSERT INTO DRIVER VALUES (2020,'C','X');


SELECT driver.Year, driver.Code, driver.Type, count(st.type) as count 
FROM driver 
   LEFT OUTER JOIN #senatetest2 st 
      ON driver.year = st.year
      AND driver.code=st.code
      AND driver.type = st.type;