Finding the sum of column data of each row in each day hour in postgresql

user_11554799 0
Viewed 285

I am very much new to PostgreSQL. Recently I am working on a database where there is a table name "store". store has item_id as primary key. here is a demo data in my store table :

id item_id supplier_id received quantity
12 34 6 2019-3-21 11:55:23 54
99 42 4 2019-3-21 11:23:12 98
19 39 6 2019-3-21 12:59:23 21
69 82 3 2019-3-21 10:29:11 32

I want to formulate a query that will return the number of supplies(supplies = sum of quantity) received in each day hour.the output will include the day hour sort by hour.

The output will be like this :

hour supplies
2019-3-21 11:00:00 - 11:59:59 152
2019-3-21 12:00:00 - 12:59:59 21

Can anyone help me out to formulate this query in postgreSQL ?

aggregation postgresql sql
1 Answers

You may use a calendar table approach here. Assuming we only need to cover a 24 hour period on the single date 2019-03-21, we can try:

with dates as (
    select generate_series(
       (date '2019-03-21')::timestamp,
       (date '2019-03-22')::timestamp,
       interval '1 hour'
     ) as dt
)

select
    d.dt::date::text || ' ' ||
        to_char(d.dt::time,'HH24:MM:SS') || ' - ' ||
        to_char(d.dt::time + interval '1 hour' - interval '1 second', 'HH24:MM:SS') as hour,
    coalesce(sum(s.quantity), 0) as supplies
from dates d
left join store s
    on s.received >= d.dt and s.received < d.dt + interval '1 hour'
group by
    d.dt
order by
    d.dt;

Demo

Note: If you only want to view hours which had a non zero sum of quantity, simply add the following having clause to the above query:

having sum(s.quantity) > 0
user_18632290
Related