How to zero out all negative numbers in a group-by T-SQL statement

I have a T-SQL query where I want all negative quantities to be zeroed out.

SELECT 
p.productnumber, 
v.[Description],
SUM(i.Quantity) as quantity
FROM ...
LEFT JOIN ...
LEFT JOIN ...
LEFT JOIN ...
GROUP BY productnumber, [Description]

​x
 
SELECT p.productnumber, v.[Description],SUM(i.Quantity) as quantityFROM ...LEFT JOIN ...LEFT JOIN ...LEFT JOIN ...GROUP BY productnumber, [Description]​

Basically if the sum is made up of 5, 5, -1, the result should be 5+5+0=10, and not (5+5+(-1)=9.

How would I do that?

Answer

You could use a CASE statement

SUM(CASE WHEN i.Quantity < 0 THEN 0 ELSE i.Quantity END)

 
SUM(CASE WHEN i.Quantity < 0 THEN 0 ELSE i.Quantity END)​

Or a more obscure version

SUM(NULLIF(i.Quantity, -ABS(i.Quantity)))

 
SUM(NULLIF(i.Quantity, -ABS(i.Quantity)))​

or just exclude these rows altogether in the WHERE clause if they are not needed for any other purpose.

Advertisement

Answer