Need help in T-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

Need help in T-SQL

Author

Topic

xSycopons
Starting Member

5 Posts

Posted - 2012-05-01 : 11:44:51

How would i display the sum of Total Amount per year?

DATE Amount
2009 100.00
2010 250.00
2010 180.00
2010 200.00
2010 100.00
2011 130.00
2011 115.00

The output would be this..
Year2009Amt Year2010Amt Year2011
100.00 730.00 245.00

R.P

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-01 : 11:53:16

sorry didnt get that. do you mean total amount per year? can you show your expected output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

xSycopons
Starting Member

5 Posts

Posted - 2012-05-01 : 11:58:36

The output would be this..
Year2009Amt Year2010Amt Year2011
100.00 730.00 245.00

R.P

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-01 : 12:04:40

[code]
SELECT [2009] AS Year2009Amt,
[2010] AS Year2010Amt,
[2011] AS Year2011Amt
FROM Table t
PIVOT (SUM(Amount) FOR [Date] IN ([2009],[2010],[2011]))p
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

xSycopons
Starting Member

5 Posts

Posted - 2012-05-01 : 12:13:49

When i tried to execute the code i get this output :
different from what i want. :)
Year2009Amt Year2010Amnt Year2010Amt
NULL 100000.00 NULL
NULL NULL 200000.00
NULL 200000.00 NULL
NULL 150000.00 NULL
NULL 200000.00 NULL

S

R.P

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-01 : 12:17:32

quote:
Originally posted by xSycopons

When i tried to execute the code i get this output :
Year2009Amt Year2010Amnt Year2010Amt
NULL 100000.00 NULL
NULL NULL 200000.00
NULL 200000.00 NULL
NULL 150000.00 NULL
NULL 200000.00 NULL

S

R.P

that means you've some other column in your table which you've not shown us so far. How do you think we can guess that?
Anyways try this too. In future please give us full picture to avoid issues like this


SELECT [2009] AS Year2009Amt,
[2010] AS Year2010Amt,
[2011] AS Year2011Amt
FROM (SELECT [Date],SUM(Amount) AS Amount
      FROM Table 
      GROUP BY [Date]
      )t
PIVOT (SUM(Amount) FOR [Date] IN ([2009],[2010],[2011]))p

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

xSycopons
Starting Member

5 Posts

Posted - 2012-05-01 : 12:35:20

Thanks visakh16 i really appreciated your help . .It works fine :) Thank you so much :)..God Bless

R.P

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources