Group by Month and Year problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Group by Month and Year problem

Author

Topic

benricho
Yak Posting Veteran

84 Posts

Posted - 2002-01-21 : 17:45:40

My table looks like this:


Media
----------
mediaNo    int
title      nvarchar(100)
mediaDate  smalldatetime
mediaFile  nvarchar(150)

And I want to display a summary of media appearances for a given month, and display it like:


Janurary 2002 (12)
December 2001 (17)
November 2001 (4)

I have the following query, but it's not giving me the right solution:


SELECT DateName(month, D.dates) + ' ' + DateName(year, D.Dates) MonthName,
Count(*) media
FROM media m INNER JOIN 
(SELECT mediaDate dates FROM media) D
ON m.mediaDate=D.dates
GROUP BY DatePart(mm, D.Dates), DatePart(yy, D.Dates), DateName(month, D.dates) + ' ' + DateName(year, D.Dates)
ORDER BY DatePart(yy, D.Dates) DESC, DatePart(mm, D.Dates) DESC

The problem is that it's giving me the total of all months for the first result, so the above data would look like this:

 
Janurary 2002 (33)
December 2001 (17)
November 2001 (4)

Note that Janurary is 33, not 12. Any ideas?

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-21 : 17:55:45

SELECT DateName(month, mediaDate) + ' ' + DateName(year, mediaDate) MonthName,Count(*)
FROM media m
GROUP BY DateName(month, mediaDate) + ' ' + DateName(year, mediaDate)
ORDER BY DatePart(yy, mediaDate) DESC, DatePart(mm, mediaDate) DESC

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

benricho
Yak Posting Veteran

84 Posts

Posted - 2002-01-21 : 18:07:00

Thanks nr, I just had to put the ORDER BY items in the GROUP BY clause and it worked.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-21 : 18:10:58

Did you also notice that you probably don't need the derived table?

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

benricho
Yak Posting Veteran

84 Posts

Posted - 2002-01-21 : 18:59:32

Yeah, I trying to make it harder than it had to be. Lots to learn.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources