Count (Add and substract) - 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

SQL Server Development (2000)

Count (Add and substract)

Author

Topic

jparker
Posting Yak Master

118 Posts

Posted - 2005-04-01 : 09:02:37

I have three lines which belong to the same job

Name | Job Id | Value | Type
-----------------------------
Jparker | 00001 | 100.99 | I
Jparker | 00001 | 50.00 | I
Jparker | 00001 | 75.80 | C
Monkey | 00002 | 60.00 | I
Monkey | 00002 | 20.00 | C

What I want to do is have a sum of the three values to Jparker so that i returns 75.19 and Monkey returns 40.

The logic is that I denotes invoice and C denotes credit note so in the first example 100.99 + 50.00 - 75.80

How do I do this on the fly in sql i.e. make all Type where it is C a negative number and then sum that group?

mr_mist
Grunnio

1870 Posts

Posted - 2005-04-01 : 09:09:00

I think something like

SELECT [Name], sum (case when type = I then value when type = C then (-1 * value) else 0 end) as VALUE
FROM yourtable
GROUP BY [Name]

Best not to use Name as a column name btw

-------
Moo. :)

jparker
Posting Yak Master

118 Posts

Posted - 2005-04-01 : 09:30:01

Cheers dude spot on

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources