I'm trying to calculate the average number of calls for a given weekday by hour of the day. I've tried nesting aggregates byt it always comes out to 1, rather than the actual average.
Here's what the data looks like
creation_ts weekday hour
2013-04-01 20:27:41 Mon 8:00 PM
2013-04-02 15:56:55 Tue 3:00 PM
2013-04-03 11:54:33 Wed 11:00 AM
2013-04-03 23:51:22 Wed 11:00 PM
2013-04-04 17:53:45 Thu 5:00 PM
2013-04-05 10:56:53 Fri 10:00 AM
2013-04-05 15:19:25 Fri 3:00 PM
2013-04-06 03:56:32 Sat 3:00 AM
2013-04-06 14:58:46 Sat 2:00 PM
I'm needing to generate a result set that has an average number of calls for the hour for each weekday.
Weekday 00:00 01:00 02:00 03:00 .... 22:00 23:00
Sun 0 2 4 5 18 16
I tried using the following statement (by an example for dnoeth in previous similar post) but it only totals accross all days hour, rather than average
**Weekday and "hour" are columns from my temp table where I've extracted both from the timestamp
avg(cnt) as CountByDayOfWeek
count(*) as cnt
group by 1,2
group by 1,2
Any help is greatly appreciated!
You group both times by the same columns, if i understodd you correctly you have to group by date/hour first and then by weekday:
creation_dt (FORMAT 'eee') (CHAR(3))
,AVG(CASE WHEN "hour" = 0 THEN cnt END) AS "00:00"
,AVG(CASE WHEN "hour" = 1 THEN cnt END) AS "01:00"
,AVG(CASE WHEN "hour" = 2 THEN cnt END) AS "02:00"
CAST(creation_ts AS DATE) AS creation_dt
,EXTRACT(HOUR FROM creation_ts) AS "hour"
,COUNT(*) AS cnt
GROUP BY 1,2
) AS dt
GROUP BY 1
Thank you Dieter! I wound up using the calendar to divide by the the number of days in the date range, rather than only days with data. You've helped me tremendously!
cast(calls.CountbyDayofWeek as decimal(15,3)) / dt.num_of_days as AVG_COUNT
WHEN 1 THEN 'Sun'
WHEN 2 THEN 'Mon'
WHEN 3 THEN 'Tue'
WHEN 4 THEN 'Wed'
WHEN 5 THEN 'Thu'
WHEN 6 THEN 'Fri'
WHEN 7 THEN 'Sat'
END as DayofWeek,
count(*) as num_of_days
where calendar_date Between '2013-03-07' AND '2013-05-07'
group by 1
EXTRACT(HOUR FROM case_creation_ts) AS "hour" ,
Count (*) as CountbyDayofWeek,
CASE ((cast(case_creation_ts as date) - Date '1900-01-01') Mod 7) +1
WHEN 7 THEN 'Sun'
WHEN 1 THEN 'Mon'
WHEN 2 THEN 'Tue'
WHEN 3 THEN 'Wed'
WHEN 4 THEN 'Thu'
WHEN 5 THEN 'Fri'
WHEN 6 THEN 'Sat' END as DayofWeek
Group by 1,3
)calls on dt.dayofweek = calls.dayofweek
order by 1,2
I used the suggestion from Ulrich in a previous post, from: