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