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

How to return 0 for all time intervals instead of nothing when counting
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.
