CODEWITHSUNDEEP
sqlpostgresql
Sreehari
Sreehari

Reputation: 1370

Query to retrieve count per hour and zero if none

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);

Upvotes: 2

Views: 1123

Answers (3)

JohnHC
JohnHC

Reputation: 11205

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

Upvotes: 0

KIKO Software
KIKO Software

Reputation: 16771

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.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

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;

Upvotes: 4

Related Questions