Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

TSQL Sum operation

Posted on 2006-03-22
4
Medium Priority
?
599 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
0
Comment
Question by:puckkk
4 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 750 total points
ID: 16257149
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
0
 
LVL 39

Expert Comment

by:appari
ID: 16257206
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
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16257222
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
             
 
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 16261335
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]
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question