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:

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 :)

Discuss this article: 16 Comments so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

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

How to Download Ringtones (1 Reply)

Database Diagram not showing relationship of table (3 Replies)

SQL2008R2: Should there be a limit xmla qry size? (3 Replies)

Variables in Where Clause (20 Replies)

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

How to migrate Replication DB on sql server 2008 (7 Replies)

Reading XML in SQL (5 Replies)

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

Subscribe to

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

- Advertisement -