asked on

TSQL Sum operation

Ok, I'm not so good at TSQL. Ihave followinga table:

Operation ID | Description | Cost ID | Earn ID | Value
0 | abc | 20000 | 25000 | 57,38
1 | abc | 25000 | 30000 | 4.153,22
2 | abc | 30000 | 40010 | 833,44
3 | abc | 20000 | 55000 | 1.467,12
4 | abc | 20500 | 20000 | 135,01
5 | abc | 40000 | 40010 | 77,00
...

Value is type money. Cost ID and Earn ID are char (5) and they can be either in column cost ID or in column Earn ID.
I want to sum all the Values of all the rows whith the same Cost - Earn ID with Earn ID with negative value. I mean, I want a select statement resulting the following (I want the Value column with the sum done, of course):

Cost - Earn ID | Value
20000 | = + 57,38 +1.467,12 - 135,01
20500 | = + 135,01
25000 | = - 57,38 + 4.153,22
...

Any TSQL Statement suggestion (the smart as possible :) )?
Thanx a lot

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

appari

i think you need group by in main sql too

select id, sum(value)
from (
select cost_id as ID, sum(value) as value from yourtable group by cost_id
union all
select earn_id as ID, -sum(value) from yourtable group by earn_id
) as l
group by Id

Lowfatspread

select id as [Cost - Earn id],sum(value) as Value
from
(select case x when 1 then Costid else Earnid end as ID
,Case x when 1 then Value else -Value end as Value
from (select 1 as x union select 2 ) as a,
YourTable
) as Z
Group by ID
Order BY 1

Scott Pletcher

SELECT CASE WHEN costOrEarn = 'C' THEN Costid ELSE Earnid END AS [Cost - Earn ID],
SUM(CASE WHEN costOrEarn = 'C' THEN value ELSE -value END) AS [Value]
FROM tableName
CROSS JOIN (
SELECT 'C' AS costOrEarn UNION ALL
SELECT 'E'
) AS costOrEarnFlags
GROUP BY CASE WHEN costOrEarn = 'C' THEN Costid ELSE Earnid END
ORDER BY [Cost - Earn ID]