Determine Average Count by Day of Week

Database

Determine Average Count by Day of Week

How would I compute an AVERAGE for each day of week between a date range i.e. August through September on a Count?   An average for FRI, SAT, SUN etc.  E.g if there are 6 Thursdays in the date range, the answer below would be 7 for Thursday. 






AREA_NM_Turf CountByDayofWeek DayofWeek
West 31 Fri
West 7 Sun
West 41 Wed
West 42 Thu
West 29 Mon
West 43 Tue
West 24 Sat

SELECT

AREA_NM_TURF,

Count (*) as CountbyDayofWeek

CASE ((CMTMT_DT - 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

 

 

FROM VCTD485

Where (ASKME_CREAT_DT) Between '2012-08-01' AND '2012-08-30'

AND CMTMT_DT = ASKME_CREAT_DT

And Area_NM_TURF like 'AL%PCA'

Group by AREA_NM_Turf, DAyofWeek

order by ARea_NM_Turf

8 REPLIES
N/A

Re: Determine Average Count by Day of Week

What do you mean by "6 thursdays in the date range", the number of thursdays in the calendar or the number of thursdays with data in your table?

#2 is easy using nested aggregates:

SELECT
AREA_NM_TURF,
CMTMT_DT (format 'eee') (char(3)) as DayofWeek,
avg(cnt) as CountbyDayofWeek
from
(
SELECT
AREA_NM_TURF,
CMTMT_DT,
Count (*) as cnt
FROM
...
group by 1,2
) as dt
group by 1,2

Dieter

N/A

Re: Determine Average Count by Day of Week

you need to count the number of days per weekday separatly and join this to your result set.

Something like:

select d.AREA_NM_TURF,
d.DayofWeek,
casT(d.CountbyDayofWeek as decimal(15,3)) / w.num_of_days as AVG_COUNT
from
(
select
CASE day_of _week
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
from sys_calendar.calendar
where calendar_date Between '2012-08-01' AND '2012-08-30'
group by 1
) w
join
(
SELECT
AREA_NM_TURF,
Count (*) as CountbyDayofWeek,
CASE ((CMTMT_DT - 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
FROM VCTD485
Where (ASKME_CREAT_DT) Between '2012-08-01' AND '2012-08-30'
AND CMTMT_DT = ASKME_CREAT_DT
And Area_NM_TURF like 'AL%PCA'
Group by AREA_NM_Turf, DAyofWeek
) as d
on d.DayofWeek = w.DayofWeek

order by d.AREA_NM_TURF,
d.DayofWeek
N/A

Re: Determine Average Count by Day of Week

As you see here you need to be very precise in the desciption of your problem as you might get different results.

The main difference in the two solutions from Dieter and me is that Dieter is counting the number of days per weekdays with data in a date range and my solution is counting the days per weekdays in a time range - without reference of the presence to data.

In case for each day at least one records exists in your table the result is the same but the result will be different if at least one day is not present for all group by values in your base table.

Re: Determine Average Count by Day of Week

This works great.  Thank you for the explanation.  How would you order the days of week like Sunday, Monday, Tuesday etc?   

N/A

Re: Determine Average Count by Day of Week

So, what is your actual requirement?

Why not sort by 

((CMTMT_DT - Date '1900-01-01') Mod 7) + 1

 

?

 

 


Re: Determine Average Count by Day of Week

I would  like to order by the order in a week:

East   Sun     

East Monday    

East Tuesday etc.

West  Sun

West Monday

West Tuesday etc.

 But it now orders them alphabetically.  

N/A

Re: Determine Average Count by Day of Week

select d.AREA_NM_TURF,
d.DayofWeek,
casT(d.CountbyDayofWeek as decimal(15,3)) / w.num_of_days as AVG_COUNT
from
(
select day_of _week,
CASE day_of _week
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
from sys_calendar.calendar
where calendar_date Between '2012-08-01' AND '2012-08-30'
group by 1
) w
join
(
SELECT
AREA_NM_TURF,
Count (*) as CountbyDayofWeek,
CASE ((CMTMT_DT - 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
FROM VCTD485
Where (ASKME_CREAT_DT) Between '2012-08-01' AND '2012-08-30'
AND CMTMT_DT = ASKME_CREAT_DT
And Area_NM_TURF like 'AL%PCA'
Group by AREA_NM_Turf, DAyofWeek
) as d
on d.DayofWeek = w.DayofWeek

order by d.AREA_NM_TURF,
w.day_of _week

Re: Determine Average Count by Day of Week

Ulrich, 

Is it possible to add to the previous query incorporating a result set that contains the year with previous year on the SAME row?

E.g.  If i select the dates Between '2011-01-01' and '2011-10-01'  and between '2012-01-01' and '2012-10-01' have the result set like:   

HYear        Area_NM_Turf             DayofWeek                 Avg_ Count     PreviousYrAVG_Count

2012      East                               Sun.                            14                     7

2012      East                               Mon. etc                       12                     15

I found an example in a book where they do something like current.year = previous.year+1.  But that was only self joining an alias of one table.