Skip to content
Advertisement

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]

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?

Advertisement

Answer

You could use a CASE statement

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

Or a more obscure version

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