# Group By using Weeks

By Bill Graziano on 2 October 2000 | 16 Comments | 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 datetimeReviewScore 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 ArticlesGROUP 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 in SQL Server (30 July 2007)

## Other Recent Forum Posts

Variables in Where Clause (20 Replies)

how to update 2 records on 1 field (20 Replies)

Reading XML in SQL (5 Replies)

Return a zero value in place of null (8 Replies)

## Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.