Conditional SUM 1

[firstdivision]

Hi,

I did find a solution to this problem on my own, but it's really inefficient (I joined two sub-queries together), so I wanted to ask here because I'm sure there's a more elegant solution.

Basically it's an accounting problem, where the data (debits and credits) is stored as follows:

(many columns hidden)
---------------------
|Amount|Code|Account|
|   100|   D|      1|
|  -100|   C|      1|
|  -100|   D|      2|
---------------------

What I need to do is find a query that will ADD when the code is "D" and subtract when the code is "C" (i.e. Debits minus Credits). So the total of all the rows from above would be 100:

100 - (-100) + (-100) = 100

After a grouping, I'm looking to display something like:

---------------
|Total|Account|
|  200|      1|
| -100|      2|
---------------

I'm sure this is a common problem and I'm just missing an obviously easy way to solve it...

Thanks,
FD

 

[vongrunt]

Assuming Account can be either C or D and nothing else:

select Account, sum(Amount*case when Code='D' then 1 else -1 end) as [Total]
from myTable
group by Account

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[vongrunt]

... correction: Code, not Account.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[firstdivision]

I knew there had to be a slick way to do it.

I'll give that a shot tomorrow.

Thanks.

 

[firstdivision]

Thanks vongrunt, worked like a charm.