CODEWITHSUNDEEP
sqldatabasepostgresql
ikuuu
ikuuu

Reputation: 3

Count by hour interval

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.

Upvotes: 0

Views: 980

Answers (1)

Michel Milezzi
Michel Milezzi

Reputation: 11155

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)

Upvotes: 2

Related Questions