Grouping and Summing Data - 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 and Summing Data

Author

Topic

anderskd
Starting Member

25 Posts

Posted - 2007-01-08 : 13:04:23

Hello,
This might be easy for some in this forum, but I'm stuck right now on it. I am working on a project where I am consolidating some data. I need to sum the data from one column (AMOUNT) based on the value in another column(TYPE), grouped by a third (ID). I tried to put together a small example dataset.

AMOUNT | ID | TYPE
$10 | 555 | Y
$12 | 555 | Y
$13 | 555 | N
$14 | 555 | N
$16 | 125 | N

Basically it need to group by ID and put the amounts in AMTY if the value of TYPE is Y and AMTN if the value is N.

The result should be something like this
ID | AMTY | AMTN
555 | $22 | $27
125 | $0 | $16

I'm having trouble with the grouping and CASE statements that are needed to separate and sum the dollar amounts.

Thanks for the help. Let me know if I can provide additional information.

CPA and computer dork...how nerdy is that?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-08 : 13:11:28

select id, sum(case when type = 'y' then amount else 0 end) as amty,
sum(case when type = 'n' then amount else 0 end) as amtn
group by id
order by id desc

Peter Larsson
Helsingborg, Sweden

anderskd
Starting Member

25 Posts

Posted - 2007-01-08 : 13:26:32

Thanks Peso!
That is exactly what I was trying to do.
I kept trying to do the sum inside of the case statement - and kept getting errors.

This makes sense now. Thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources