CODEWITHSUNDEEP
sqlsql-server
TheDon
TheDon

Reputation: 69

MSSQL Query - Sum only positive numbers

I have a MSSQL query where I sum the value of columns. For an example;

select SUM(column_a), SUM(column_b)
from table c
group by c_id

Now I need to sum only positive numbers, ignore negative numbers. For an example:

Column A:
2
-2
4
2

The SUM function will give me result of 6 (2-2+4+2) What I need it to do is to ignore the negative number (-2) and give me result of 8 (2+4+2)

I have 8 columns where I need it.

Upvotes: 6

Views: 16981

Answers (4)

jarlh
jarlh

Reputation: 44796

Use case expressions to do conditional aggregation:

select sum(case when a > 0 then a else 0 end),
       sum(case when b > 0 then b else 0 end)
       ...
from tablename

Add GROUP BY if needed:

group by c_id

Upvotes: 12

Shilpa Soni
Shilpa Soni

Reputation: 2142

select sum(column_a), sum(column_b) from table c where column_a>0 and column_b>0 group by c_id

Upvotes: 0

DrLazer
DrLazer

Reputation: 3123

Cant you just not select them in your query with a simple WHERE clause?

WHERE  Number > 0;

Upvotes: 2

Vishwanath Dalvi
Vishwanath Dalvi

Reputation: 36661

SELECT SUM(Number)
FROM   TableName 
WHERE  Number > 0;

Upvotes: 5

Related Questions