Question 180839 in PostgreSQL Question posted in
The official documentation can be found here.

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

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

Tags:

2

Answers


  1. 0

    You can do it using the conditional aggregation :

        select deployTime as times ,   
           sum(case when hotfix then 1 else 0 end) as "Change Count" 
    from deployments 
    group by deployTime
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search