Database
Enthusiast

## 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 Area_NM_TURF like 'AL%PCA'

Group by AREA_NM_Turf, DAyofWeek

order by ARea_NM_Turf

8 REPLIES
Senior Apprentice

## 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 CountbyDayofWeekfrom (  SELECT    AREA_NM_TURF,    CMTMT_DT,    Count (*) as cnt  FROM  ...  group by 1,2 ) as dtgroup by 1,2`

Dieter

Supporter

## 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_COUNTfrom(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_daysfrom sys_calendar.calendarwhere calendar_date Between '2012-08-01' AND '2012-08-30'group by 1) wjoin(SELECTAREA_NM_TURF,Count (*) as CountbyDayofWeek, CASE ((CMTMT_DT - Date '1900-01-01') Mod 7) + 1WHEN 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 DayofWeekFROM VCTD485Where (ASKME_CREAT_DT) Between '2012-08-01' AND '2012-08-30'AND CMTMT_DT = ASKME_CREAT_DTAnd 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`
Supporter

## 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.

Enthusiast

## 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?

Supporter

## 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`   `?`
Enthusiast

## 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.

Supporter

## 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_COUNTfrom(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_daysfrom sys_calendar.calendarwhere calendar_date Between '2012-08-01' AND '2012-08-30'group by 1) wjoin(SELECTAREA_NM_TURF,Count (*) as CountbyDayofWeek, CASE ((CMTMT_DT - Date '1900-01-01') Mod 7) + 1WHEN 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 DayofWeekFROM VCTD485Where (ASKME_CREAT_DT) Between '2012-08-01' AND '2012-08-30'AND CMTMT_DT = ASKME_CREAT_DTAnd 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 `

Enthusiast

## 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.