CODEWITHSUNDEEP
postgresql
Abdusoli
Abdusoli

Reputation: 659

How to GROUP BY hour and take missing hours as 0 in POSTGRESQL

I am making GROUP BY hour for one day. In this case there are some time intervals with no data. How can i take these missing hours as 0 value?. Then there would be always 23 value in my query result. Below what I tried so far.

SELECT COUNT(*), extract(hour from created_date) as hourr 
FROM client_requests 
WHERE created_date > '2020-02-24 00:00:00' and created_date < '2020-02-24 23:59:59' 
GROUP BY extract(hour from created_date) 
ORDER BY extract(hour from created_date) ASC

result is like below:

count                |     hourr    |
------------------------------------
1                    |      0
1                    |      6
5                    |      8
14                   |      9
35                   |      10
37                   |      11
40                   |      12
32                   |      13
18                   |      14
28                   |      15
39                   |      16
31                   |      17
30                   |      18
16                   |      19
7                    |      20
11                   |      21
14                   |      22

But i need like this

count                |     hourr    |
------------------------------------
1                    |      0
0                    |      1
0                    |      2
0                    |      3
0                    |      4
0                    |      5
1                    |      6
0                    |      7
5                    |      8
14                   |      9
35                   |      10
37                   |      11
40                   |      12
32                   |      13
18                   |      14
28                   |      15
39                   |      16
31                   |      17
30                   |      18
16                   |      19
7                    |      20
11                   |      21
14                   |      22
0                    |      23

Upvotes: 1

Views: 464

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247950

Left join with a generated table of hours:

SELECT coalesce(count(r.*), 0) AS count,
       h.h as hour
FROM generate_series(0, 23) AS h
   LEFT JOIN client_requests AS r
      ON extract(hour from r.created_date) = h.h
         AND r.created_date >= '2020-02-24 00:00:00'
         AND r.created_date < '2020-02-25 00:00:00' 
GROUP BY h.h
ORDER BY h.h;

Upvotes: 3

Related Questions