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

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

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

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