SQL Max solution (something, 0)

Question

I would like to do the following:

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

as in, I want the 10,000 field to be summed, but if the 10,000 field is <0, then I want it to add 0.

any suggestions?

Charles

+2

Karl 10 Sep '09 at 11:02

1 answer

Quassnoi · Accepted Answer · 2009-09-10T11:06:05+0000

ANSI

(supported SQL Server

, Oracle

, MySQL

and PostgreSQL

):

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

Usage GREATEST

(not supported SQL Server

):

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