Select Sum group by - 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

Select Sum group by

Author

Topic

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2010-01-12 : 03:14:51

Hi,

I have a data table as below:

Yr Mth Quantity
_______________
2009 1 10000
2009 1 20000
2009 2 30000
2009 3 30000
2009 3 20000
2009 4 80000
2009 4 80000

How to i query it as below:

Yr 1 2 3 4
_________________________________
2009 30000 30000 50000 160000

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-01-12 : 03:20:13

[code]
DECLARE @t TABLE (Yr INT, Mth INT,Quantity INT)
INSERT INTO @t SELECT
2009, 1, 10000 UNION ALL SELECT
2009, 1, 20000 UNION ALL SELECT
2009 ,2, 30000 UNION ALL SELECT
2009, 3, 30000 UNION ALL SELECT
2009 ,3, 20000 UNION ALL SELECT
2009, 4, 80000 UNION ALL SELECT
2009, 4, 80000

SELECT yr,[1],[2],[3],[4]
FROM @t s
PIVOT (SUM(Quantity) FOR mth IN ([1],[2],[3],[4]))pvt

SELECT yr,SUM(CASE WHEN mth = 1 THEN quantity END) AS '1',
SUM(CASE WHEN mth = 2 THEN quantity END) AS '2',
SUM(CASE WHEN mth = 3 THEN quantity END) AS '3',
SUM(CASE WHEN mth = 4 THEN quantity END) AS '4'
FROM @t GROUP BY yr
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-12 : 03:28:39

if your months cant be determined before use either of below

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2010-01-13 : 04:51:27

quote:

Originally posted by bklr


DECLARE @t TABLE (Yr INT, Mth INT,Quantity INT)
INSERT INTO @t SELECT
2009, 1, 10000 UNION ALL SELECT 
2009, 1, 20000 UNION ALL SELECT 
2009 ,2, 30000 UNION ALL SELECT 
2009, 3, 30000 UNION ALL SELECT 
2009 ,3, 20000 UNION ALL SELECT 
2009, 4, 80000 UNION ALL SELECT 
2009, 4, 80000

SELECT yr,[1],[2],[3],[4]
FROM @t s
PIVOT (SUM(Quantity) FOR mth IN ([1],[2],[3],[4]))pvt

SELECT yr,SUM(CASE WHEN mth = 1 THEN quantity END) AS '1',
SUM(CASE WHEN mth = 2 THEN quantity END) AS '2',
SUM(CASE WHEN mth = 3 THEN quantity END) AS '3',
SUM(CASE WHEN mth = 4 THEN quantity END) AS '4'
FROM @t GROUP BY yr

Thanks bklr

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2010-01-13 : 04:52:33

quote:
Originally posted by visakh16

if your months cant be determined before use either of below

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Yes, visakh16. Thanks. I will read through the two link.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 05:43:56

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources