Group By using Weeks

By Bill Graziano on 2 October 2000 | Tags: Queries , SELECT

---

Robert writes "I have a database that contains articles that are rated between 0 and 10. I want to show the average score for articles on a week-by-week basis. In other words, I want to output something like this:
Week commencing 27/1/00 Average Score = 3.7/10
Week commencing 3/2/00 Average Score = 5/10
...and so on...

In other situations, I would use GROUP BY but I don't see how I can do that with a range of dates. Can you help???"

I think we can make this one work. Please not that I'm going to use American date formats since that's easier for me. This will work equally well no matter what your date format. The first step is to convert all your dates to the first day of the week. Let's suppose your table looks like this:

Articles
---------------------
ReviewDate datetime
ReviewScore tinyint

I'll use the DATEPART function to strip out pieces of a date. For example,

select datepart(dw, '10/2/2000')

returns 2 since October 2nd is a Monday and the second day of the week. Now I can determine what day of the week each date falls on. I can subtract that from each date to convert them back to the first day of the week. That query looks something like this:

SELECT Week = DateAdd(day, -1 * datepart(dw, ReviewDate), ReviewDate )
FROM Articles

I'm using the DATEADD function to subtract the day of the week from each date. Now for the GROUP BY part. This is actually a little tricky. You can't group by WEEK since it is an alias for a function. What you can do is group by the actual function. Adding the average looks like this:

SELECT Week = DateAdd(day, -1 * datepart(dw, ReviewDate), ReviewDate ),
Avg_Score = Avg(convert(float, ReviewScore))
FROM Articles
GROUP BY DateAdd(day, -1 * datepart(dw, ReviewDate), ReviewDate )

Remember that I have to convert ReviewScore to a floating point number since it is TINYINT in my table.

Also keep in mind that you can use the SET DATEFIRST to determine which day of the week is considered the first day of the week. Happy article reviewing :)

Related Articles Using Dynamic SQL in Stored Procedures (7 March 2011) Joining to the Next Sequential Row (2 April 2008) Writing Outer Joins in T-SQL (11 February 2008) Aggregating Correlated Sub-Queries (23 October 2007) How to Use GROUP BY with Distinct Aggregates and Derived tables (31 July 2007) How to Use GROUP BY in SQL Server (30 July 2007) Returning Complex Data from User-Defined Functions with CROSS APPLY (11 June 2007) SQL Server 2005: Using OVER() with Aggregate Functions (21 May 2007)

Other Recent Forum Posts Query performance Call Center data (21h) Looking for on Premises tool to read data from SQL logs and populate data warehouse (1d) Possible SQL 2014 to 2016 Issue - Some Application Functions Slow/Failing (1d) Working with multiple WHERE statements (1d) Create a new field value that shows a difference in value from 2 fields (3d) Hierarchy wise Sales Targets (3d) Get the MaxDate in results (5d) Switch Statement within a SSRS SQL Expression (6d)