CODEWITHSUNDEEP
sqloracle-database
TrevorBrooks
TrevorBrooks

Reputation: 3850

Oracle Avg Number of Calls Per Hour By Year

I have an Oracle table filled with call information. I have the call datetime field (calldate). Each record represents one call. How do I find the average number of calls per hour by year?

Sample data:
Calldate                               Account     Name
1/20/2016 10:16:09 AM      12345         Blee
1/20/2016 11:17:02 AM      45678         Foo

Something like:

1:00 AM 23
2:00 AM 22
3:00 AM 19

Thank you!

Upvotes: 0

Views: 932

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

Something like:

select to_char(calldate, 'YYYY-HH24') as yyyyhh,
       count(*) / count(distinct trunc(calldate)) as avg_per_hour
from t
group by to_char(calldate, 'YYYY-HH24')
order by yyyyhh;

Note: This treats days with no calls as NULL, rather than zero.

Upvotes: 2

Related Questions