Group by 15 minutes increments with a count, including counts of 0

March 24, 2023

I have the below query which groups my data in 15 minute increments but it does not include increments that don’t have data in that 15 minute increment.

Current query:

SELECT 
    TO_CHAR(TRUNC(time_stamp)
        + FLOOR(TO_NUMBER(TO_CHAR(time_stamp, 'SSSSS'))/900)/96, 'YYYY-MM-DD HH24:MI:SS') time_start,
    COUNT (CUSTOMERS) Customer_Calls
FROM CUSTOMERS
WHERE time_stamp >= to_date('2023-03-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
GROUP BY
    TRUNC(time_stamp) + FLOOR(TO_NUMBER(TO_CHAR(time_stamp, 'SSSSS'))/900)/96;

current output:

2023-03-23 00:30:00 1
2023-03-23 00:45:00 1
2023-03-23 01:45:00 1
2023-03-23 03:45:00 1

I’m looking to get every increment of 15 minutes. As an example:

Requested output

2023-03-23 00:00:00 0
2023-03-23 00:15:00 0
2023-03-23 00:30:00 1
2023-03-23 00:45:00 1
2023-03-23 01:00:00 0
2023-03-23 01:15:00 0
2023-03-23 01:30:00 0
2023-03-23 01:45:00 1
and so on.

Any help would be appreciated. Thanks!

>Solution :

Not all timeslots are avaialble in the table to start with, so we would need to generate them first; for this, we can use recursion . Then, we bring the customers table with a left join.

Assuming that you want all timeslots from yesterday at midnight to now:

with slots (ts) as (
    select trunc(sysdate) - interval '1' day ts from dual
    union all
    select ts + interval '15' minute from slots where ts < sysdate
)
select s.ts, count(c.time_stamp) as customer_call
from slots
left join customers c 
    on c.time_stamp  >= s.ts
    and c.time_stamp <  s.ts + interval '15' minute
group by s.ts