How set 0 with MAX function when it is NULL?
29,971
Solution 1
Well, as there is no date like 2014, you would expect null, because the maximum of nothing is actually not anyting.
But do this:
COALESCE(MAX(number),0)
Which means: get the first non-null thing from the next list, so if your max
is null, it'll give you 0
Solution 2
COALESCE works, but IFNULL seems clearer to me.
IFNULL(MAX(number), 0)
If the first expression is not NULL, IFNULL() returns the expression itself, otherwise it returns the second parameter. IFNULL() returns a numeric or string value, depending on the context in which it is used.
Author by
Donovant
Updated on July 09, 2022Comments
-
Donovant almost 2 years
I would like to understand how to set 0 value of the attribute when it is NULL with MAX function. For example:
Name columns: number - date Values: 10 - 2012-04-04 11 - 2012-04-04 12 - 2012-04-04 13 - 2012-04-15 14 - 2012-06-21 1 - 2013-07-04
Number is incremental field, but it has set itself 1 when new year has come. But result of:
SELECT (MAX(number)+1) number WHERE date LIKE "2014%"
is NULL and not 1 because MAX(number) is NULL and not 0