SQL Max(something,0) type solution

Question

Hi

I would like to do the following:

SELECT sum(max(field-10,000,0)) from TABLE

as in, I want to have field-10,000 summed up, but if field-10,000 < 0 then I want it to add 0.

any suggestions?

Karl

Answer 1

ANSI syntax (supported by SQL Server, Oracle, MySQL and PostgreSQL):

SELECT  SUM(CASE WHEN FIELD < 10000 THEN 0 ELSE FIELD - 10000 END)
FROM    mytable

Using GREATEST (not supported by SQL Server):

SELECT  SUM(GREATEST(field - 10000, 0))
FROM    mytable