Replace NULL with SUBTOTAL and TOTAL in a ROLLUP

Question

I have tried to use IFNULL to replace the NULL fields returned by ROLLUP for subtotals and totals but it doesn't appear to be working.

Query:

select IFNULL(usergroups.name, 'GROUP') AS DEALER,
IFNULL(users.name, 'TOTAL') AS SERVICE_ADVISOR,
COUNT(DISTINCT vcrs.uid) AS COMPLETED,
..........
..........
and vcrs.vcrSubStatus = 4
group by DEALER, SERVICE_ADVISOR with ROLLUP;

Output:

DEALER          SERVICE_ADVISOR COMPLETED   IDENTIFIED  AUTHORISED
Aston Martin    Chris           3           664.56      0
Aston Martin    Graham          6           0           0
Aston Martin    (NULL)          15          664.56      0
Bentley         Sukraj          1           0           0
Bentley         Terry           4           0           0
Bentley         (NULL)          5           0           0
Jaguar          Emma            10          2448        1224
Jaguar          Paul            1           0           0
Jaguar          Simon           7           2754        918
Jaguar          (NULL)          18          5202        2142
(NULL)          (NULL)          2611        96591.62    42130.14

Desired Output:

DEALER          SERVICE_ADVISOR COMPLETED   IDENTIFIED  AUTHORISED
Aston Martin    Chris           3           664.56      0
Aston Martin    Graham          6           0           0
Aston Martin    TOTAL           15          664.56      0
Bentley         Sukraj          1           0           0
Bentley         Terry           4           0           0
Bentley         TOTAL           5           0           0
Jaguar          Emma            10          2448        1224
Jaguar          Paul            1           0           0
Jaguar          Simon           7           2754        918
Jaguar          TOTAL           18          5202        2142
GROUP           TOTAL           2611        96591.62    42130.14

Answer 1

I'm only 2 years too late, but since I came across the same issue as @the_gimlet I thought I'd post the answer.

So don't know if this is a mySQL versioning or something, but using mysql 5.6 I get the same problem... ifnull will not replace the rollup 'nulls'.

Simply get around this by making your rollup a subquery, and doing the ifnulls in the main select... annoying to repeat the select, but it works!

e.g. for example above

SELECT 
  IFNULL(`DEALER`, 'GROUP') AS DEALER,
  IFNULL(`SERVICE_ADVISOR`, 'TOTAL') AS SERVICE_ADVISOR,
  `COMPLETED`,
  /* .......... */
FROM (SELECT 
        usergroups.name AS DEALER,
        users.name AS SERVICE_ADVISOR,
        COUNT(DISTINCT vcrs.uid) AS COMPLETED,
      /* .......... */
      AND       vcrs.vcrSubStatus = 4
      GROUP BY  DEALER, SERVICE_ADVISOR with ROLLUP);