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

Solution 1

To resolve the issue with COALESCE/IFNULL still returning NULL for the WITH ROLLUP placeholders, you need to GROUP BY the table column names, rather than the aliased column expressions.

The issue is caused by the GROUP BY clause being specified on the aliased column expressions, because the aliases are assigned after the column expression is processed.
Resulting in the WITH ROLLUP NULL placeholders not being in the recordset to be evaluated by COALESCE.
Meaning the aliases DEALER, SERVICE_ADVISOR in the GROUP BY do not exist until after IFNULL/COALESCE have already been executed. See MySQL Handling of GROUP BY for more details.

Example DB-Fiddle

CREATE TABLE foo (
  `amount` INTEGER,
  `created` INTEGER
);

INSERT INTO foo
  (`amount`, `created`)
VALUES
  ('1', '2019'),
  ('2', '2019');

Query #1 (Reproduce Issue)

SELECT
    SUM(amount) AS amounts,
    COALESCE(created, 'Total') AS created_coalesce
FROM foo
GROUP BY created_coalesce WITH ROLLUP;

| amounts | created_coalesce |
| ------- | ---------------- |
| 3       | 2019             |
| 3       |                  |

Query #2 (Corrected)

SELECT
    SUM(amount) AS amounts,
    COALESCE(created, 'Total') AS created_coalesce
FROM foo
GROUP BY foo.created WITH ROLLUP;

| amounts | created_coalesce |
| ------- | ---------------- |
| 3       | 2019             |
| 3       | Total            |

---

Use-Case Specific

Example DB-Fiddle

SELECT
    COALESCE(usergroups.name, 'GROUP') AS DEALER,
    COALESCE(users.name, 'TOTAL') AS SERVICE_ADVISOR,
    COUNT(DISTINCT vcrs.uid) AS COMPLETED,
/* ... */
GROUP BY usergroups.name, users.name WITH ROLLUP;

Query #1 (Original)

SELECT
    COALESCE(usergroups.name, 'GROUP') AS DEALER,
    COALESCE(users.name, 'TOTAL') AS SERVICE_ADVISOR,
    COUNT(DISTINCT vcrs.uid) AS COMPLETED
/* ... */
GROUP BY DEALER, SERVICE_ADVISOR WITH ROLLUP;

| DEALER | SERVICE_ADVISOR | COMPLETED |
| ------ | --------------- | --------- |
| Foo    | Jane Doe        | 1         |
| Foo    | John Doe        | 1         |
| Foo    |                 | 2         |
|        |                 | 2         |

Query #2 (Corrected)

SELECT
    COALESCE(usergroups.name, 'GROUP') AS DEALER,
    COALESCE(users.name, 'TOTAL') AS SERVICE_ADVISOR,
    COUNT(DISTINCT vcrs.uid) AS COMPLETED
/* ... */
GROUP BY usergroups.name, users.name WITH ROLLUP;

| DEALER | SERVICE_ADVISOR | COMPLETED |
| ------ | --------------- | --------- |
| Foo    | Jane Doe        | 1         |
| Foo    | John Doe        | 1         |
| Foo    | TOTAL           | 2         |
| GROUP  | TOTAL           | 2         |

---

Considerations

1. With MySQL 5.7+ and ONLY_FULL_GROUP_BY enabled, selected non-aggregate columns that are not specified in the GROUP BY clause will fail. Meaning the following query will not work as expected: DB-Fiddle

   SELECT COALESCE(YEAR(foo), 'foo') /* ... */ GROUP BY YEAR(foo) WITH ROLLUP
   -> ER_WRONG_FIELD_WITH_GROUP: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.foo_bar.foo' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
   

2. COALESCE, IFNULL, IF(... IS NULL) and CASE WHEN ... IS NULL will all function similarly. Where IFNULL is the proprietary to MySQL and is a less functional replacement of COALESCE. As COALESCE can accept more than two parameters to check against NULL, returning the first non-NULL value.

   mysql> SELECT COALESCE(NULL, NULL, 1, NULL);
          -> 1
   
   mysql> SELECT IFNULL(NULL, 1);
          -> 1
   
   mysql> SELECT IF(NULL IS NULL, 1, '');
          -> 1
   
   mysql> SELECT CASE WHEN NULL IS NULL THEN 1 END;
          -> 1
   

3. nullable columns in the GROUP BY as either aliases or column names, will result in the NULL values being displayed as the WITH ROLLUP placeholder. This applies to using WITH ROLLUP in general. For example if users.name can return NULL. DB-Fiddle

   | DEALER | SERVICE_ADVISOR | COMPLETED | 
   | ------ | --------------- | --------- | 
   | Foo    | TOTAL           | 1         |
   | Foo    | Jane Doe        | 1         |
   | Foo    | John Doe        | 1         |
   | Foo    | TOTAL           | 3         |
   | GROUP  | TOTAL           | 3         |
   

---

Prevent NULL column values from being displayed

To ensure that nullable columns are not accidentally included, you would need to specify in the criteria to exclude them.

Example DB-Fiddle

SELECT
    COALESCE(usergroups.name, 'GROUP') AS DEALER,
    COALESCE(users.name, 'TOTAL') AS SERVICE_ADVISOR,
    COUNT(DISTINCT vcrs.uid) AS COMPLETED
FROM vcrs
LEFT JOIN users
ON users.id = vcrs.uid
LEFT JOIN usergroups
ON usergroups.id = users.group_id
WHERE vcrs.vcrSubStatus = 4
AND users.name IS NOT NULL
GROUP BY usergroups.name, users.name
WITH ROLLUP;

Result

| DEALER | SERVICE_ADVISOR | COMPLETED |
| ------ | --------------- | --------- |
| Foo    | Jane Doe        | 1         |
| Foo    | John Doe        | 1         |
| Foo    | TOTAL           | 2         |
| GROUP  | TOTAL           | 2         |

Since LEFT JOIN is used on the vcrs table, IS NOT NULL must be applied to the WHERE clause, instead of the ON clause. As LEFT JOIN returns NULL for non-matching criteria. To circumvent the issue, use an INNER JOIN to limit the resultset to only those with matching ON criteria.

/* ... */
INNER JOIN users
ON users.id = vcrs.uid
AND users.name IS NOT NULL
/* ... */
WHERE vcrs.vcrSubStatus = 4
GROUP BY usergroups.name, users.name
WITH ROLLUP;

---

Include NULL columns values

To explicitly include nullable column values, without duplicating the WITH ROLLUP placeholder name, you would need to utilize a derived table subquery to substitute the NULL value as a textual value.

Example DB-Fiddle

SELECT 
    COALESCE(v.usergroup_name, 'GROUP') AS DEALER,
    COALESCE(v.user_name, 'TOTAL') AS SERVICE_ADVISOR,
    COUNT(DISTINCT v.uid) AS COMPLETED
FROM (
  SELECT
      usergroups.name AS usergroup_name,
      COALESCE(users.name, 'NULL') AS user_name,
      vcrs.uid
  FROM vcrs
  LEFT JOIN users
  ON users.id = vcrs.uid
  LEFT JOIN usergroups
  ON usergroups.id = users.group_id
  WHERE vcrs.vcrSubStatus = 4
) AS v
GROUP BY v.usergroup_name, v.user_name
WITH ROLLUP;

Result

| DEALER | SERVICE_ADVISOR | COMPLETED |
| ------ | --------------- | --------- |
| Foo    | Jane Doe        | 1         |
| Foo    | John Doe        | 1         |
| Foo    | NULL            | 1         |
| Foo    | TOTAL           | 3         |
| GROUP  | TOTAL           | 3         |

---

You can also optionally replace the 'NULL' textual placeholder as desired and even display it as NULL.

SELECT
    COALESCE(v.usergroup_name, 'GROUP') AS DEALER,
    CASE v.user_name WHEN 'NULL' THEN NULL ELSE COALESCE(v.user_name, 'TOTAL') END AS SERVICE_ADVISOR,
    COUNT(DISTINCT v.uid) AS COMPLETED
FROM (
    /* ... */
) AS v
GROUP BY v.usergroup_name, v.user_name
WITH ROLLUP;

Solution 2

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);

Solution 3

Do you want something like this?

SELECT COALESCE(usergroups.name, 'GROUP') AS DEALER,
COALESCE(users.name, IF(usergroups.name IS NULL, 'TOTAL', 'SUBTOTAL')) AS SERVICE_ADVISOR,
COUNT(DISTINCT vcrs.uid) AS COMPLETED,
..........
..........
AND vcrs.vcrSubStatus = 4
GROUP BY DEALER, SERVICE_ADVISOR with ROLLUP;

Test:

mysql;root@localhost(playground)> select * from t;
+------+----------+-------+--------+
| id   | car      | state | tstamp |
+------+----------+-------+--------+
|    1 | toyota   | new   |   1900 |
|    2 | toyota   | old   |   1950 |
|    3 | toyota   | scrap |   1980 |
|    4 | mercedes | new   |   1990 |
|    5 | mercedes | old   |   2010 |
|    6 | tesla    | new   |   2013 |
+------+----------+-------+--------+
6 rows in set (0.04 sec)

mysql;root@localhost(playground)> select car, sum(tstamp) from t group by car with rollup;
+----------+-------------+
| car      | sum(tstamp) |
+----------+-------------+
| mercedes |        4000 |
| tesla    |        2013 |
| toyota   |        5830 |
| NULL     |       11843 |
+----------+-------------+
4 rows in set (0.03 sec)

mysql;root@localhost(playground)> select coalesce(car, 'huhu'), sum(tstamp) from t group by car with rollup;
+-----------------------+-------------+
| coalesce(car, 'huhu') | sum(tstamp) |
+-----------------------+-------------+
| mercedes              |        4000 |
| tesla                 |        2013 |
| toyota                |        5830 |
| huhu                  |       11843 |
+-----------------------+-------------+
4 rows in set (0.00 sec)

Comments

[the_gimlet]

thanks for the quick response - just tried your suggestion but it hasn't made a difference, still returning NULL as it did when I was using IFNULL....?

[fancyPants]

@the_gimlet Are you sure, you're not mixing something up? Works fine in my case. See my edited answer for proof.

[the_gimlet]

I copied and pasted the COALESCE exactly as you wrote it, but there was no change in the output. The query includes joins, would that affect it at all?

[fancyPants]

The code should definitely work, your code too. If you see no difference, you're mixing something else up. Stupid question, don't take this offensive, but are you looking at a newly generated result?

[the_gimlet]

no offence taken. yes i've re-run it a few times, switching back and forth between IFNULL and COALESCE but the result doesn't change. I am very new at this but managed to get everything else working correctly. Strange, and very frustrating, that I've snagged on this one simple thing. what else could i have mixed up that would affect it?

[neevek]

Works like a charm!

[Dave P]

if nothing else, at least @the_gimlet now knows they weren't going mad :)

[Fuseteam]

works great! now only if i not replace null if it is the grant total column 😂

[Christos Karapapas]

This would create confusion in case there is grouping for a nullable field, wouldn't it?

[Will B.]

@ChristosKarapapas updated my answer to explain the nullable columns issue and workarounds.

[Will B.]

This works because the table column name car is used as opposed to an alias, which is applied prior to the rows benng grouped. COALESCE() and IFNULL() function similarly. If you use COALESCE(car, 'huhu') AS car_name ... GROUP BY car_name the same issue is experienced. See db-fiddle.com/f/sCAsSDe2DmzMh1YdzWzpZV/0 for an example.