MySQL group by with rollup, coalesce/ifnull, and date functions

Question

I have run into an issue with MySQL's ROLLUP and dealing with the resulting NULLs. The IFNULL/COALESCE functions work well with plain columns, but seem to break down when used with date functions. Examples follow:

SELECT
    YEAR(date_time) AS Year,
    count(x) AS Count
FROM mytable
GROUP BY year WITH ROLLUP

returns (as expected)

Year Count
---- -----
2015 3
2016 2
NULL 5

When I query for non-date columns (varchar, for example), I can deal with the NULL values by using IFNULL or COALESCE functions to replace NULL values with strings. However, when I apply the same logic to the above query, it does not seem to work.

SELECT
    COALESCE(YEAR(date_time), 'moo') AS Year,
    count(x) AS Count
FROM mytable
GROUP BY year WITH ROLLUP

or

SELECT
    IFNULL(YEAR(date_time), 'moo') AS 'year',
    count(x) AS Count
FROM mytable
GROUP BY year WITH ROLLUP

returns

Year Count
---- -----
2015 3
2016 2
NULL 5

instead of expected

Year Count
---- -----
2015 3
2016 2
moo  5

Any ideas, suggestions?

Answer 1

If you're trying to target the NULL's generated during the aggregate rollup calculations (as opposed to NULLs in your raw data), you're referring to the wrong NULLs. If so, this is probably what you are trying to do:

SELECT
    IFNULL(m.year, 'moo')
FROM
    (SELECT
        YEAR(date_time) AS 'year'
     FROM
        mytable
     GROUP BY
        year
     WITH ROLLUP) m

Here's the sqlfiddle.