SUM per month and compare to two years

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

SUM per month and compare to two years

Author

Topic

Chewbacca
Starting Member

5 Posts

Posted - 2011-06-13 : 05:32:19

Hey,

In my table I have the following fields:
Region
Amount
Posting_Date
I want to sum all transactions per month and then compare them with the year before. Since we do not have a full 2011 yet I want the columns without data for 2011 to be filled by zero 8but not for the current month).

I would like the data to be displayed like this:

Region1 Month 2010 2011
Jan 122 98
Feb 11 88
. . .
Oct 671 0
Nov 519 0
Dec 400 45
Region2 Jan 331 330
Feb 231 111
. . .
. . .

I guess this is pretty easy stuff, but all help would be appreciated.

Chewbacca
Starting Member

5 Posts

Posted - 2011-06-13 : 05:40:37

The formatting did not come out right, so I'll try to explain instead:

I want the result to be grouped by region and month, and the amount should be summarized per year.

ayadav0984
Starting Member

21 Posts

Posted - 2011-06-13 : 06:11:17

Try this out :

select REGION
isnull(sum(case when TrnxMnth ='JAN-11' then amt end)/100000,0) as [JAN-10],
isnull(sum(case when TrnxMnth ='FEB-11' then amt end)/100000,0) as [FEB-10],
isnull(sum(case when TrnxMnth ='MAR-11' then amt end)/100000,0) as [MAR-10],
isnull(sum(case when TrnxMnth ='APR-11' then amt end)/100000,0) as [APR-10],
isnull(sum(case when TrnxMnth ='MAY-11' then amt end)/100000,0) as [MAY-10],
isnull(sum(case when TrnxMnth ='JUN-11' then amt end)/100000,0) as [JUN-10],
isnull(sum(case when TrnxMnth ='JUL-11' then amt end)/100000,0) as [JUL-10],
isnull(sum(case when TrnxMnth ='AUG-11' then amt end)/100000,0) as [AUG-10],
isnull(sum(case when TrnxMnth ='SEP-11' then amt end)/100000,0) as [SEP-10],
isnull(sum(case when TrnxMnth ='OCT-11' then amt end)/100000,0) as [OCT-10],
isnull(sum(case when TrnxMnth ='NOV-11' then amt end)/100000,0) as [NOV-10],
isnull(sum(case when TrnxMnth ='DEC-11' then amt end)/100000,0) as [DEC-10],
from dbo.COMMISSION_DB_APR10_MAY11 group by REGION

hope this will work..

Chewbacca
Starting Member

5 Posts

Posted - 2011-06-13 : 06:38:35

Thank you. Most of it I have figured out but what is TrnxMnth ?

ayadav0984
Starting Member

21 Posts

Posted - 2011-06-14 : 01:22:45

its just the name of the field having month as the value...i used trnxmnth as the short form for transaction month ..which could be anything u want..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources