How to return 0 for all time intervals instead of nothing when counting

Question

I have a query for deployment table. There is no data for hotfix column now. I want to show all change count without hotfix and with hotfix for time intervals.

Table data:

deployTime	changeno	hotfix
2022-08	aaa
2022-08	bbb
2022-11	ccc
2023-01	ddd

First attempted query:

SELECT deployTime               AS times ,   
       COUNT(DISTINCT changeno) AS "Change Count" 
FROM deployments 
WHERE hotfix = ''
GROUP BY deployTime 

which returns all dates with Change count:

times	ChangeCount
2022-08	2
2022-11	1
2023-01	1

Second attempted query:

SELECT deployTime               AS times ,   
       COUNT(DISTINCT changeno) AS "Change Count" 
FROM deployments 
WHERE hotfix != ''
GROUP BY deployTime 

which returns no records if there's no record with hotfix != ''.

How we can get 0 count for every date instead of nothing?

times	HotfixCount
2022-08	0
2022-11	0
2023-01	0

Thanks

Answer 1

The problem with your query is that you're using a WHERE clause, that removes records. What you should do instead, is apply conditional aggregation on presence/absence of hotfix values:

SELECT deployTime AS times ,   
       COUNT(DISTINCT changeno) FILTER(WHERE hotfix = '') AS "Change Count" 
FROM deployments 
GROUP BY deployTime 

And change it to WHERE NOT hotfix = '' conversely to obtain zeroes.

Check the demo here.

Note: It's better to have NULL values instead of empty strings, when you need to indicate missing data.