How to reset all negative numbers in a group - using the T-SQL statement

Question

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] 

In principle, if the sum consists of 5, 5, -1, the result should be 5 + 5 + 0 = 10, and not (5 + 5 + (- 1) = 9.

How can I do it?

Answer 1

You can use the 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 simply completely exclude these lines in the WHERE if they are not needed for any other purpose.

Answer 2

just filter out the ones you don't want ...

 WHERE quantity > 0 

Answer 3

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

Answer 4

I know that this has long been answered, but I think it's worth mentioning that the ground floor formula is:

 (x+|x|)/2 

In SQL Server, this is:

 SELECT (i.Quanity - ABS(i.Quanity))/2 AS Quanity FROM .... 

This is found in many computer scenarios. Not sure if this is the most efficient way to do this in TSQL or logical operators like CASE will be faster. But it works 100% of the time every time.