CODEWITHSUNDEEP
sql-server
CyberK
CyberK

Reputation: 1578

Invert some values in SQL Server

I have a table like this one:

ID   |   Description  |  Account  |  DepAccount  |  Value
----------------------------------------------------------
0    |  Something     | 110       | 0            | 500,50
1    |  Something x   | 0         | 110          | -320,95

Now I need a query to sum the values but there is an issue with it.

Like in the example, if the account is 110, it is fine, if the DepAccount is 110, I need to invert the value so I sum 320,95 instead of -320,95

So the query should return value: 821,45

So something like: 

SELECT SUM(Value) As Total 
FROM Table 
WHERE Account = 110 OR DepAccount = 110 WHERE etc...

Who can help? I'm using SQL Server 2008 Express Edition

Upvotes: 2

Views: 7323

Answers (4)

a1ex07
a1ex07

Reputation: 37382

Assuming one of Account and DepAccount is always 0, you can write

SELECT 
CASE 
 WHEN DepAccount = 0 THEN Account
 WHEN Account = 0  THEN DepAccount
END AS acct,
SUM(abs(Value)) AS Total
FROM Table
GROUP BY 
 CASE 
 WHEN DepAccount = 0 THEN Account
 WHEN Account = 0  THEN DepAccount
END

Upvotes: 0

Philip Kelley
Philip Kelley

Reputation: 40359

The sample set is small so it's a bit unclear what you're trying to do, but it sounds something like this:

SELECT
  sum(case
        when Account = 110 then Value
        when DepAccount = 100 then -1 * Value
        else 0
      end)
from MyTable

That assumes that "invert" means "reverse the sign". If you only need to make negatives positive, use

SELECT
  sum(case
        when Account = 110 then Value
        when DepAccount = 100 then abs(Value)
        else 0
      end)
from MyTable

Upvotes: 2

Joe Stefanelli
Joe Stefanelli

Reputation: 135848

Perhaps I'm oversimplifying, but could you do this:

select sum(abs(Value)) as Total
    from Table
    where Account = 110
        or DepAccount = 110

Upvotes: 0

nathan gonzalez
nathan gonzalez

Reputation: 12017

SELECT SUM(ABS(Value)) As Total FROM Table WHERE Account = 110 OR DepAccount = 110 WHERE etc

that should get you what you need. the abs function returns the absolute value of the number.

Upvotes: 0

Related Questions