group by month sql - 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 month sql

Author

Topic

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2011-01-23 : 04:08:41

Hi, I have transaction details with transaction date

ID TransactionDate Amount
1 20/01/2011 100.00
2 21/01/2011 200.00
3 01/02/2011 250.00

How can I write my sql so that it will show something like this:

Jan Feb Mar Apr May June July Aug Sept Oct Nov Dec
300 250 0 0 0 0 0 0 0 0 0 0

I means it will group by Month and how can I determine which month contain no data and it will show 0.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-23 : 07:31:02

That isn't really group by, look into the PIVOT operator.

Otherwise, you would have to to Case When for each columnm

SELECT
SUM (Case When month(TransactionDate) = 1 then Amount else 0 end) as JAN,
SUM (Case When month(TransactionDate) = 2 then Amount else 0 end) as FEB,
SUM (Case When month(TransactionDate) = 3 then Amount else 0 end) as MAR,
SUM (Case When month(TransactionDate) = 4 then Amount else 0 end) as APR,
SUM (Case When month(TransactionDate) = 5 then Amount else 0 end) as MAY,
SUM (Case When month(TransactionDate) = 6 then Amount else 0 end) as JUN
SUM (Case When month(TransactionDate) = 1 then Amount else 0 end) as JAN,
....etc

FROM [Table]

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources