Query to retrieve count per hour and zero if none

Question

I am trying all the ways, but I am not successful so far.

I have my data in the below way. I am using postGreSQL

order_id |      create_date
    -----+--------------------
    6000 | 2013-05-09 11:53:04
    6001 | 2013-05-09 12:58:00
    6002 | 2013-05-09 13:01:08
    6003 | 2013-05-09 13:01:32
    6004 | 2013-05-09 14:05:06
    6005 | 2013-05-09 14:06:25
    6006 | 2013-05-09 14:59:58
    6007 | 2013-05-09 19:00:07

I need a query which produces the count of orders per hour for all the 24 hours. If there are no orders in an hour, query output should be zero by default. Below should be the output format.

    orders |      hour
    -----+--------------------
    0    | 00:00
    0    | 01:00
    0    | 02:00
    0    | 03:00
    0    | 04:00
    0    | 05:00
    0    | 06:00
    0    | 07:00
    0    | 08:00
    0    | 09:00
    0    | 10:00
    1    | 11:00
    1    | 12:00
    2    | 13:00
    3    | 14:00
    0    | 15:00
    0    | 16:00
    0    | 17:00
    0    | 18:00
    1    | 19:00
    0    | 20:00
    0    | 21:00
    0    | 22:00
    0    | 23:00

Is it possible to do? Below is my current query. Ofcourse it is not giving the output in the way that I desire.

select count(order_id) as orders, date_trunc('hour', create_date) as hr from order_table where date_trunc('day', create_date)='2013-05-09' GROUP BY date_trunc('hour', create_date);

On which DBMS are you working? MySQL or PostgreSQL? Also, can you please post the table schema? — Thomas Berger, Jul 26 '18 at 09:51
This may help. https://www.plumislandmedia.net/mysql/filling-missing-data-sequences-cardinal-integers/ — O. Jones, Jul 26 '18 at 09:55
I closed the question while it still had a MySQL tag. I've nominated it for reopening. Others can do the same. — O. Jones, Jul 26 '18 at 09:58

score 4 · Accepted Answer · answered Jul 26 '18 at 09:57

4

You need to generate the hours. Here is one method using generate_series():

select '00:00'::time + g.h * interval '1 hour',
       count(order_id) as orders
from generate_series(0, 23, 1) g(h) left join
     order_table ot
     on extract(hour from create_date) = g.h and
        date_trunc('day', create_date) = '2013-05-09'
group by g.h
order by g.h;

Or alternatively:

select g.dte, count(order_id) as orders
from generate_series('2013-05-09'::timestamp, '2013-05-09 23:00:00'::timestamp, interval '1 hour') g(dte) left join
     order_table ot
     on g.dte = date_trunc('hour', create_date) 
group by g.dte
order by g.dte;

answered Jul 26 '18 at 09:57

Gordon Linoff

1,242,037
58
646
786

In the first query output, the hour column have the output format as "01:00:00". How can we change it to "01:00"? – Sreehari Jul 26 '18 at 10:41
First answer give the exact what in the question for me. – Dasun Aug 31 '21 at 07:57

JohnHC · Answer 2 · 2018-07-26T10:20:15.527

0

Use a numbers CTE (Postgresql example):

with Nums(NN) as
(
values(0)
union all
select NN+1
where NN <23
)
select NN as the_hour, count(order_id) as orders
from Nums
left join order_table
on date_part('hour',create_date) = NN
group by NN

edited Jul 26 '18 at 10:20

answered Jul 26 '18 at 09:53

JohnHC

10,935
1
24
40

It is giving me an error like "column "nn" does not exist". Do I need to replace NN with date every where? – Sreehari Jul 26 '18 at 10:09

score 0 · Answer 3 · answered Jul 26 '18 at 09:59

Can I still give an answer? Test, test, oh yes it works. Ok, I think the problem in your query is that your comparison is wrong: date_trunc('day', create_date)='2013-05-09' should be: date_trunc('day', create_date)='2013-05-09 00:00:00'. So like this:

SELECT COUNT(order_id) as orders, 
       date_trunc('hour',create_date) as hr 
FROM order_table 
WHERE date_trunc('day', create_date) = '2013-05-09 00:00:00' 
GROUP BY date_trunc('hour',create_date);

This does however not returns zero counts, but others have solved that problem.

Query to retrieve count per hour and zero if none

3 Answers3

Linked