Need big help : Sum and Substract

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 big help : Sum and Substract

Author

Topic

AxeMaster
Starting Member

2 Posts

Posted - 2007-09-19 : 19:00:09

Hi,
to keep things short, I have a query like this : SELECT A AS A, SUM(B) AS B FROM CC WHERE A IN (1,2,3) GROUP BY A

I want to be able to SUM the field B when A is equal to 1, and substract the field B when A is equal to 2 or 3. So I know SUM is incorrect there, cuz sometimes the field B must be added, and sometimes it must be substracted. What's the easiest way to do it?

For example, if the fields B in the database has the values 3, 5, and 7, the query would go something like 3+5-7, giving 1 for B at the end.

Tanx for your help guyz!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-19 : 19:07:49

[code]SELECT A AS A,
SUM(CASE A WHEN 1 THEN B ELSE -B END) AS B
FROM CC
WHERE A IN (1,2,3)
GROUP BY A[/code]

KH
[spoiler]Time is always against us[/spoiler]

AxeMaster
Starting Member

2 Posts

Posted - 2007-09-19 : 19:11:22

WoW! Fast reply. Tanx a bunh d00d!

- Disengaging auto scratching head and pulling hairs -

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-20 : 02:28:29


SELECT A AS A,
       SUM(B*CASE A WHEN 1 THEN 1 ELSE -1 END) AS B
FROM   CC
WHERE  A IN (1,2,3)
GROUP BY A

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources