We help IT Professionals succeed at work.

# TSQL Sum operation

on
Medium Priority
627 Views
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
Comment
Watch Question

## View Solution Only

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Commented:
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
CERTIFIED EXPERT
Top Expert 2011

Commented:
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

Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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]
##### Thanks for using Experts Exchange.

• View three pieces of content (articles, solutions, posts, and videos)
• Ask the experts questions (counted toward content limit)
• Customize your dashboard and profile