Count rows for each distinct column values in result set and display zeros

Question

I have the following table

name	type	created_at
John	student	2022-10-01
Tom	student	2022-10-02
Susan	teacher	2022-10-10

I need a query to get the count for each distinct value in type column in filtered result and display zero's if no instances of this type exist in result.

I tried the following

SELECT type, COUNT(*)
    FROM tablename
    where created_at between '2022-10-01' and '2022-10-02'
    group by type;

which will give

type	count
student	2

I need:

type	count
student	2
teacher	0

Answer 1

Use conditional aggregation with a calendar table approach:

SELECT t1.type, COUNT(t2.type) AS count
FROM (SELECT DISTINCT type FROM tablename) t1
LEFT JOIN tablename t2
    ON t2.type = t1.type AND
       t2.created_at BETWEEN '2022-10-01' AND '2022-10-02'
GROUP BY t1.type;

Note that ideally you should have some other table which stores all types. Then, you could use that in place of the inline distinct query I have above.