user1471980 user1471980 - 20 days ago 6
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 11.01.43.255000000 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

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,
       count(*)
from t
group by trunc(as_time, 'HH'),
         floor(extract(minute from as_time)
order by 1, 2;

Notes:

  • 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.
Comments