Count by hour interval

Question

i have written an SQL query in postgresql that works fine it gets me the number of work done per employee for every hour

SELECT COUNT(work_done) AS count, EXTRACT(hour from start_time) AS hour FROM c_call WHERE start_time >= '2018-10-13 00:00:00' GROUP BY employee_id;

it's perfect if an emplyee was actif ine every interval hour but when an hour has no data for an employee it is omitted . how can make it work so that the result contains a row for each interval with the value field set to zero if the employee didnt work at that hour.

A row with 0 for every non-worked hour? That will be a lot of rows. — jarlh
– jarlh, Commented Mar 21, 2018 at 15:38
yes but i can then process that data elsewhere ( i only need the data for the day so it wont be too much rows ) — ikuuu
– ikuuu, Commented Mar 21, 2018 at 15:39
What exactly is the point of such a return value? That's not something that's particularly easy to do in SQL, and honestly, I'm struggling to think of why you'd want to do so here as opposed to application logic. — Jacobm001
– Jacobm001, Commented Mar 21, 2018 at 15:39
it's because i need to display this data in a google table chart and for the google table chart i need to specify each column value ( the google table chart is drawn dynamically ) so it would be much easier to send data like that — ikuuu
– ikuuu, Commented Mar 21, 2018 at 15:41
Have a help table (or recursive cte) with a row for each hour. Right join that table. — jarlh
– jarlh, Commented Mar 21, 2018 at 15:42

Michel Milezzi · Accepted Answer · 2018-03-21 17:20:04Z

You can generate a hour series using generate_series function:

SELECT * FROM generate_series(0, 23) AS foo(bar)

And then use it to fill the hour gaps:

WITH employee_call AS ( SELECT COUNT(work_done) AS count, EXTRACT(hour from start_time) AS hour_fraction FROM c_call WHERE start_time >= '2018-10-13 00:00:00' GROUP BY employee_id ), hour_series (hour_fraction) AS ( SELECT generate_series(0, 23) ) SELECT COALESCE(c.count, 0) AS count, COALESCE(c.hour_fraction, h.hour_fraction) AS hour_fraction FROM hour_series h LEFT JOIN employee_call c ON (c.hour_fraction = h.hour_fraction)

Collectives™ on Stack Overflow

Count by hour interval

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related