check if a value is NULL or Less than 0 in one TSQL statement

Question

ISNULL(SUM(MyTable.Total), 0) AS Total

how can i modify the above statement to also check if Total is less than 0 (zero), such that if Total is NULL or less than 0 (negative), i assign 0 to Total

Answer 1

CASE 
WHEN COALESCE(SUM(MyTable.Total), 0) <= 0 THEN 0
ELSE SUM(MyTable.Total)
END AS [Total]

Answer 2

CASE WHEN 
  COALESCE(SUM(MyTable.Total),0) <= 0 
THEN 
   0 
ELSE 
  SUM(MyTable.Total)
END AS Total

Answer 3

CASE WHEN ISNULL(SUM(MyTable.Total), 0) <= 0 THEN 0
     ELSE SUM(MyTable.Total)
END AS Total

Answer 4

How about

SUM(ISNULL(MyTable.Total, 0)) AS Total

I prefer this as the NULL implementation in databases is not always logical and does differ between vendors and whether ANSI_NULLS are enabled or not.

E.g. SUM of NULL, NULL and 1 is returned as 1, but (1 + NULL + NULL) is equal to NULL ...

You can then do the less than 0 with the CASE as above, thus

CASE 
WHEN SUM(ISNULL(MyTable.Total,0)) <= 0 THEN 0
ELSE SUM(ISNULL(MyTable.Total,0))
END AS [Total]