Grouping 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

SQL Server 2000 Forums

Transact-SQL (2000)

Grouping problem

Author

Topic

forwheeler
Starting Member

44 Posts

Posted - 2008-09-25 : 10:02:12

I want the result set to display like this

transyear code budget ytd
2006 12345 10000.00 500.00
2007 12345 20000.00 700.00

Here are my queries

SELECT trans_year, code,
SUM(amount) AS amount
FROM dbo.table (NOLOCK)
WHERE trans_year IN ('2006','2007','2008')
AND gl_account IN ('4000','5000')
GROUP BY trans_year, code, gl_account
ORDER BY trans_year, code

The budget amount is from gl_account 4000 and the ytd is from gl_account 5000. I would like to have this query return on one row the amounts like above. I'm not sure how to group it so it returns correctly. Right now it returns one 2 seperate rows because of the grouping of course.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-25 : 10:16:04

sum(amount) is budget column?

E 12°55'05.63"
N 56°04'39.26"

forwheeler
Starting Member

44 Posts

Posted - 2008-09-25 : 10:34:08

It is the budget and the YTD Spent depending on the gl_account.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-25 : 10:38:19

See TRANSFORM or PIVOT queries...

SELECT		trans_year,
		code,
		SUM(CASE WHEN gl_account = 4000 THEN amount ELSE 0 END) AS amount1,
		SUM(CASE WHEN gl_account = 5000 THEN amount ELSE 0 END) AS amount2
FROM		dbo.Table
WHERE		trans_year IN (2006, 2007, 2008)
		AND gl_account IN (4000, 5000)
GROUP BY	trans_year,
		code
ORDER BY	trans_year,
		code

E 12°55'05.63"
N 56°04'39.26"

forwheeler
Starting Member

44 Posts

Posted - 2008-09-25 : 10:49:30

Thats works great! Thanks for the fast response!

forwheeler
Starting Member

44 Posts

Posted - 2008-09-25 : 15:05:49

Well I thought I was done. They want to see the data like this now.

code 2006budget 2006ydt 2007budget 2007ytd 2007budget 2007ytd

I looked at the crosstab stuff you suggested using the case statement but I think this is more than a crosstab. I can do this with a table variable but I would rather not do that if there is a better solution.

forwheeler
Starting Member

44 Posts

Posted - 2008-09-25 : 16:07:16

I got it the way I need it with this

SELECT code,
--2006
SUM(CASE WHEN gl_account = '4000'
THEN
CASE WHEN trans_year = '2006'
THEN amount*-1 ELSE 0 END
ELSE 0 END) AS Budget2006,

SUM(CASE WHEN gl_account = '5000'
THEN
CASE WHEN trans_year = '2006'
THEN amount ELSE 0 END
ELSE 0 END) AS YTD2006,

--2007
SUM(CASE WHEN gl_account = '4000'
THEN
CASE WHEN trans_year = '2007'
THEN amount*-1 ELSE 0 END
ELSE 0 END) AS Budget2007,

SUM(CASE WHEN gl_account = '5000'
THEN
CASE WHEN trans_year = '2007'
THEN amount ELSE 0 END
ELSE 0 END) AS YTD2007,

--2008
SUM(CASE WHEN gl_account = '4000'
THEN
CASE WHEN trans_year = '2008'
THEN amount*-1 ELSE 0 END
ELSE 0 END) AS Budget2008,

SUM(CASE WHEN gl_account = '5000'
THEN
CASE WHEN trans_year = '2008'
THEN amount ELSE 0 END
ELSE 0 END) AS YTD2008

FROM dbo.table WITH (NOLOCK)
WHERE trans_year IN ('2006', '2007', '2008')
AND gl_account IN ('4000', '5000')
GROUP BY code
ORDER BY code

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 01:50:32

There is no need to overcomplicate things with nested cases...

SELECT		code,
		SUM(CASE WHEN gl_account = '4000' AND trans_year = '2006' THEN -amount ELSE 0 END) AS Budget2006,
		SUM(CASE WHEN gl_account = '5000' AND trans_year = '2006' THEN amount ELSE 0 END) AS YTD2006,
		SUM(CASE WHEN gl_account = '4000' AND trans_year = '2007' THEN -amount ELSE 0 END) AS Budget2007,
		SUM(CASE WHEN gl_account = '5000' AND trans_year = '2007' THEN amount ELSE 0 END) AS YTD2007,
		SUM(CASE WHEN gl_account = '4000' AND trans_year = '2008' THEN -amount ELSE 0 END) AS Budget2008,
		SUM(CASE WHEN gl_account = '5000' AND trans_year = '2008' THEN amount ELSE 0 END) AS YTD2008
FROM		dbo.Table WITH (NOLOCK)
WHERE		trans_year IN ('2006', '2007', '2008')
		AND gl_account IN ('4000', '5000')
GROUP BY	code 
ORDER BY	code

E 12°55'05.63"
N 56°04'39.26"

forwheeler
Starting Member

44 Posts

Posted - 2008-10-07 : 09:09:51

Yes that looks cleaner. Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources