We help IT Professionals succeed at work.

TSQL Sum operation

puckkk
puckkk asked
on
Medium Priority
627 Views
Last Modified: 2011-10-03
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

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.

Ask the Experts

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
             
 
Scott PletcherSenior 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]
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

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

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.