Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-19-2012
02:33 PM

09-19-2012
02:33 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-20-2012
12:10 AM

09-20-2012
12:10 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-20-2012
12:12 AM

09-20-2012
12:12 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-20-2012
12:28 AM

09-20-2012
12:28 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-21-2012
06:32 AM

09-21-2012
06:32 AM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-21-2012
06:59 AM

09-21-2012
06:59 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-21-2012
07:45 AM

09-21-2012
07:45 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-21-2012
08:00 AM

09-21-2012
08:00 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-26-2012
01:31 PM

09-26-2012
01:31 PM

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.