NULL not generated in last line of a GROUP BY WITH ROLLUP query

Question

I have a table containing a Date filed and a number. I want to display ordered Month names as grouping fields using a query like this:

SELECT 
  COALESCE(DATE_FORMAT(Day,'%M'), 'TOTAL') As Month,
  SUM(Money) As USD
FROM mytable
WHERE
  Day
    BETWEEN
      concat(date_format(now(),'%Y-'),'01-01')
    AND
      concat(date_format(now(),'%Y-'),'12-31')
GROUP BY
  DATE_FORMAT(Day,'%m') 
  WITH ROLLUP;

I Obtain:

+----------+--------+
| Month    | USD    |
+----------+--------+
| January  |  103.5 |
| February | 119.25 |
| March    | 163.25 |
| April    |    102 |
| May      | 124.75 |
| June     |   72.5 |
| June     | 685.25 |
+----------+--------+

Why the grand total line has no NULL value that is substitued ?

Answer 1

The problem is that your GROUP BY expression should be the same as the one in the SELECT, so thet you would have NULL against your TOTAL. You will have to rewrite the query. And I would suggest to avoid names that coincide with MySQL keywords and function names (at least quote them).

SELECT
  COALESCE(ELT(m,
               'Jan', 'Feb', 'Mar', 'Apr',
               'May', 'Jun', 'Jul', 'Aug',
               'Sep', 'Oct', 'Nov', 'Dec'),
           'TOTAL') as `Month`,
  USD
FROM (
  SELECT 
    MONTH(`Day`) As m,
    SUM(Money) As USD
  FROM mytable
  WHERE
    `Day`
      BETWEEN
        MAKEDATE(YEAR(CURDATE()), 1)
      AND
        LAST_DAY(MAKEDATE(YEAR(CURDATE()), 365))
  GROUP BY
    MONTH(`Day`) 
    WITH ROLLUP) as sums