MYSQL - ROLLUP - Ersetzen Sie die NULL mit ZWISCHENSUMME und SUMME

Wollen Sie so etwas?

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)

Das Problem wird verursacht, durch die GROUP BY segment der Spalte alias ausgeführt wird nach die Zeilen verarbeitet, die zum erstellen der alias-Werte Gruppe auf.

Als Ergebnis; NULL ist nicht in der Datensatzgruppe für IFNULL/COALESCE zu validieren gegen.

Beispiel DB-Fiddle

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

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

Abfrage #1

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

Beheben Sie das Problem mit COALESCE/IFNULL müssen Sie GROUP BY der Tabelle Spaltennamen anstatt der Aliase verarbeitet.

Anderes Thema, misstrauisch zu sein, ist mit MySQL 5.7 und ONLY_FULL_GROUP_BY, wodurch ausgewählte nicht-Aggregat-Spalten, die nicht in der GROUP BY zu scheitern. So SELECT COALESCE(YEAR(foo), 'foo') GROUP BY YEAR(foo) WITH ROLLUP wird nicht funktionieren.

Abfrage #2

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            |

Für Ihr spezifisches Problem

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

Abfrage #1

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         |

Abfrage #2

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         |

Als Randbemerkung COALESCE, IFNULL, und CASE WHEN IS NULL alle ähnlich funktionieren. Wo IFNULL ist die proprietäre MySQL und ist eine weniger funktionelle Ersatz von COALESCE. Als COALESCE annehmen kann, mehr als zwei Parameter zu überprüfen, gegen NULL wie COALESCE(NULL, NULL, NULL, 1).