All Forums Database
mattie4 9 posts Joined 08/12
19 Sep 2012
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

dnoeth 4628 posts Joined 11/04
20 Sep 2012

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

Dieter

ulrich 816 posts Joined 09/09
20 Sep 2012

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
       

 
 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

ulrich 816 posts Joined 09/09
20 Sep 2012

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.
 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

mattie4 9 posts Joined 08/12
21 Sep 2012

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

ulrich 816 posts Joined 09/09
21 Sep 2012

So, what is your actual requirement?
Why not sort by 

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

 

?

 

 

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

mattie4 9 posts Joined 08/12
21 Sep 2012

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.  

ulrich 816 posts Joined 09/09
21 Sep 2012
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 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

mattie4 9 posts Joined 08/12
26 Sep 2012

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.  
                       
 

You must sign in to leave a comment.