Group By problem - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Group By problem

Author

Topic

LOLCatLady
Starting Member

24 Posts

Posted - 2011-01-12 : 15:24:19

I created a very simple query but I'm stuck on getting my group by to work correctly. Here's my code:
SELECT CONVERT(varchar(4), gifteffdat, 100) + CONVERT(varchar(4), year(gifts.gifteffdat)) AS giftmonth, SUM(giftdeduct) AS gifttotal
FROM gifts
WHERE (gifteffdat BETWEEN CONVERT(DATETIME, '2010-03-01 00:00:00', 102) AND CONVERT(DATETIME, '2011-02-28 00:00:00', 102)) AND (giftid LIKE 'C%')

GROUP BY gifteffdat

I'm getting the following results:
giftmonth gifttotal
Mar 2010 273.00
Mar 2010 300.00
Mar 2010 30.00
Apr 2010 263.00
Apr 2010 150.00

I need the query to group each month and sum all of the total like so:
giftmonth gifttotal
Mar 2010 603.00
Apr 2010 413.00

I have a feeling I'm missing something really simple, but I just can't see it.

Thanks in advance!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-12 : 16:03:22

GROUP BY
CONVERT(varchar(4), gifteffdat, 100) + CONVERT(varchar(4), year(gifts.gifteffdat))

LOLCatLady
Starting Member

24 Posts

Posted - 2011-01-12 : 16:17:56

Perfect!

Thank you so much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources