user1471980 user1471980 - 1 year ago 62
SQL Question

how do you summarize data in oracle based sql?

I have an oracle table called login that has a column name as_time and the entry is timestamp(6) data type:

15-NOV-16 AM

I need a select statement that counts the number of records every 15 minutes for last 30 days.

Output needs to be like this:

Time Count
11/01/2016 00:00:00 10
11/01/2016 00:15:00 10
11/01/2016 00:30:00 50
11/01/2016 00:45:00 70
11/01/2016 01:00:00 10
11/01/2016 01:15:00 10
11/01/2016 01:30:00 10
11/01/2016 02:45:00 160

Can somebody help with constructing the sql query?

Answer Source

One method is to truncate to the nearest hour and then deal with the minutes separately:

select trunc(as_time, 'HH') as yyyymmddhh,
       floor(extract(minute from as_time) / 15)*15 as mm,
from t
group by trunc(as_time, 'HH'),
         floor(extract(minute from as_time)
order by 1, 2;


  • You can combine the minutes and hours back into a timestamp column if you like.
  • This only returns time periods where there is data.
  • This does not include as_date > sysdate - interval '30' day.
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download