CODEWITHSUNDEEP
sql
excelguy
excelguy

Reputation: 1624

SQL, querying sum of positive results, absolute value

I have the following query which returns a total dollar amount.

select sum(cast(dollars as dec)) from financials

This includes positive and negative values. I would like 2 separate things:

  1. How can I just query the positive dollar amounts? ie. I have 3 records, 10 , -5 , 10. result would be 20.

  2. I want an absolute value as a sum. ie. I have 3 records, 10, -5, 10. the result would be 25.

thanks.

Upvotes: 0

Views: 900

Answers (3)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

FOR 1) Use conditional SUM()

SELECT SUM( CASE WHEN dollars > 0 then dollars ELSE 0 END) as positive_sum,
       SUM( CASE WHEN dollars < 0 then dollars ELSE 0 END) as negative_sum
FROM financials

FOR 2) use ABS()

SELECT SUM( ABS( dollars ) )
FROM financials

Upvotes: 3

Yahya Mukhtar
Yahya Mukhtar

Reputation: 474

You have two queries.solutions are as follows

1.
select sum(dollars) from financials
2. 
select sum((case when dollars>0 then dollars end))+sum((case when dollars<0 then -1*dollars end)) from financials

Upvotes: 1

jose_bacoy
jose_bacoy

Reputation: 12704

Please try below queries. Thanks.

1) select sum(cast(dollars as dec)) 
from financials
where dollars > 0;

2) select sum(cast(abs(dollars) as dec)) 
from financials;

Upvotes: 1

Related Questions