SQL - Sum of positive and negative numbers using subquery

user_3095267 0
Viewed 57694

I have a table which contains positive and negative numbers. I have to find out sum of positive and negative numbers using sub query

sql
6 Answers
select sum(case when a>=0 then a else 0 end) as positive,
sum(case when a<0 then a else 0 end) as negative
from a
user_97864950 
SELECT (
    (SELECT SUM(numberColumn) FROM tableFoo WHERE numberColumn < 0 ) -
    (SELECT SUM(numberColumn) FROM tableFoo WHERE numberColumn > 0)
) AS totalCalculation
user_71761940

You can use sign to separate the values:

select Sum( ( Sign( n ) + 1 ) / 2 * n ) as PositiveSum,
  Sum( -( Sign( n ) - 1 ) / 2 * n ) as NegativeSum
  from YourTableOData;

Sign returns 1, 0 or -1 depending on the sign of the input value. A little arithmetic can convert that into 1 or 0 depending on the sign: ( Sign( n ) + 1 ) / 2 is 1 for all positive values, otherwise 0. Note that the check for negative values (( Sign( n ) - 1 ) / 2) returns -1 or 0, hence the negation (-) to avoid flipping the sign of the value that is being summed.

user_925460
Related