Database Welcome to the Teradata Forums. Read the guidelines on posting. Email notifications and RSS feeds are available, and also a chronological list of all forum activity.
 Print 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. Active Posters