Using MySQL 8.0 GROUPING() Function with GROUP BY WITH ROLLUP
This article explains MySQL 8.0's new GROUPING() function, demonstrates how it works with GROUP BY ... WITH ROLLUP to differentiate regular NULL values from roll‑up summary rows, and provides multiple SQL examples and usage tips.
MySQL 8.0 added the GROUPING() function to help identify which rows in a GROUP BY ... WITH ROLLUP result set are generated by the roll‑up operation.
GROUP BY WITH ROLLUP
The ROLLUP modifier adds a summary row for each grouping level and a grand total row for the whole result set.
Example table y1 definition:
mysql> show create table y1\G
*************************** 1. row ***************************
Table: y1
Create Table: CREATE TABLE `y1` (
`id` int NOT NULL,
`r1` int DEFAULT NULL,
`r2` int DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)Simple GROUP BY r1, r2 query:
mysql> SELECT r1, r2, COUNT(*)
-> FROM y1
-> GROUP BY r1, r2;
+------+------+----------+
| r1 | r2 | count(*) |
+------+------+----------+
| 1 | 2 | 2 |
| 2 | 5 | 2 |
| 1 | 4 | 1 |
| 4 | 3 | 4 |
| 2 | 2 | 3 |
| 4 | 4 | 2 |
| 5 | 5 | 1 |
| 4 | 5 | 1 |
| 3 | 1 | 1 |
| 5 | 2 | 1 |
| 4 | 2 | 1 |
| 3 | 2 | 1 |
+------+------+----------+
12 rows in set (0.00 sec)Adding WITH ROLLUP produces extra rows where the grouping columns are NULL :
mysql> SELECT r1, r2, COUNT(*)
-> FROM y1
-> GROUP BY r1, r2 WITH ROLLUP;
+------+------+----------+
| r1 | r2 | count(*) |
+------+------+----------+
| 1 | 2 | 2 |
| 1 | 4 | 1 |
| 1 | NULL | 3 |
| 2 | 2 | 3 |
| 2 | 5 | 2 |
| 2 | NULL | 5 |
| 3 | 1 | 1 |
| 3 | 2 | 1 |
| 3 | NULL | 2 |
| 4 | 2 | 1 |
| 4 | 3 | 4 |
| 4 | 4 | 2 |
| 4 | 5 | 1 |
| 4 | NULL | 8 |
| 5 | 2 | 1 |
| 5 | 5 | 1 |
| 5 | NULL | 2 |
| NULL | NULL | 20 |
+------+------+----------+
18 rows in set (0.00 sec)When the table contains actual NULL values, it becomes hard to tell which NULL rows are data and which are roll‑up summaries.
Inserting rows with NULL values:
mysql> insert into y1 values (21,null,null);
Query OK, 1 row affected (0.01 sec)
mysql> insert into y1 values (22,1,null);
Query OK, 1 row affected (0.01 sec)
... (additional inserts omitted for brevity) ...Running the same GROUP BY ... WITH ROLLUP now mixes data NULL s with summary NULL s.
mysql> SELECT r1, r2, COUNT(*)
-> FROM y1
-> GROUP BY r1, r2 WITH ROLLUP;
+------+------+----------+
| r1 | r2 | count(*) |
+------+------+----------+
| NULL | NULL | 1 |
| NULL | NULL | 1 |
| 1 | NULL | 1 |
| 1 | 2 | 2 |
| 1 | 4 | 1 |
| 1 | NULL | 4 |
| 2 | NULL | 1 |
| 2 | 2 | 3 |
| 2 | 5 | 2 |
| 2 | NULL | 6 |
| 3 | NULL | 1 |
| 3 | 1 | 1 |
| 3 | 2 | 1 |
| 3 | NULL | 3 |
| 4 | NULL | 1 |
| 4 | 2 | 1 |
| 4 | 3 | 4 |
| 4 | 4 | 2 |
| 4 | 5 | 1 |
| 4 | NULL | 9 |
| 5 | NULL | 1 |
| 5 | 2 | 1 |
| 5 | 5 | 1 |
| 5 | NULL | 3 |
| NULL | NULL | 26 |
+------+------+----------+
25 rows in set (0.00 sec)GROUPING() Function
The GROUPING() function returns 1 for columns that are part of a roll‑up summary row and 0 otherwise, allowing you to distinguish real NULL values from generated ones.
mysql> SELECT r1,
-> if(GROUPING(r1) = 1, 'summary', 'data') AS grouping_r1,
-> r2,
-> if(GROUPING(r2) = 1, 'summary', 'data') AS grouping_r2,
-> COUNT(*)
-> FROM y1
-> GROUP BY r1, r2 WITH ROLLUP;
+------+--------------+------+--------------+----------+
| r1 | grouping_r1 | r2 | grouping_r2 | count(*) |
+------+--------------+------+--------------+----------+
| NULL | data | NULL | data | 1 |
| NULL | data | NULL | summary | 1 |
| 1 | data | NULL | data | 1 |
| 1 | data | 2 | data | 2 |
| 1 | data | 4 | data | 1 |
| 1 | data | NULL | summary | 4 |
| 2 | data | NULL | data | 1 |
| 2 | data | 2 | data | 3 |
| 2 | data | 5 | data | 2 |
| 2 | data | NULL | summary | 6 |
| 3 | data | NULL | data | 1 |
| 3 | data | 1 | data | 1 |
| 3 | data | 2 | data | 1 |
| 3 | data | NULL | summary | 3 |
| 4 | data | NULL | data | 1 |
| 4 | data | 2 | data | 1 |
| 4 | data | 3 | data | 4 |
| 4 | data | 4 | data | 2 |
| 4 | data | 5 | data | 1 |
| 4 | data | NULL | summary | 9 |
| 5 | data | NULL | data | 1 |
| 5 | data | 2 | data | 1 |
| 5 | data | 5 | data | 1 |
| 5 | data | NULL | summary | 3 |
| NULL | summary | NULL | summary | 26 |
+------+--------------+------+--------------+----------+
25 rows in set (0.00 sec)The function also works with multiple columns; the result is a bitmask where each bit represents a column’s roll‑up status. For two columns, GROUPING(r1,r2) = GROUPING(r2) + (GROUPING(r1) << 1) . The same principle extends to more columns.
mysql> SELECT r1, r2, GROUPING(r1, r2) AS grouping_r1_r2, COUNT(*)
-> FROM y1
-> GROUP BY r1, r2 WITH ROLLUP;
+------+------+----------------+----------+
| r1 | r2 | grouping_r1_r2 | COUNT(*) |
+------+------+----------------+----------+
| NULL | NULL | 0 | 1 |
| NULL | NULL | 1 | 1 |
| 1 | NULL | 0 | 1 |
| 1 | 2 | 0 | 2 |
| 1 | 4 | 0 | 1 |
| 1 | NULL | 1 | 4 |
| 2 | NULL | 0 | 1 |
| 2 | 2 | 0 | 3 |
| 2 | 5 | 0 | 2 |
| 2 | NULL | 1 | 6 |
| 3 | NULL | 0 | 1 |
| 3 | 1 | 0 | 1 |
| 3 | 2 | 0 | 1 |
| 3 | NULL | 1 | 3 |
| 4 | NULL | 0 | 1 |
| 4 | 2 | 0 | 1 |
| 4 | 3 | 0 | 4 |
| 4 | 4 | 0 | 2 |
| 4 | 5 | 0 | 1 |
| 4 | NULL | 1 | 9 |
| 5 | NULL | 0 | 1 |
| 5 | 2 | 0 | 1 |
| 5 | 5 | 0 | 1 |
| 5 | NULL | 1 | 3 |
| NULL | NULL | 3 | 26 |
+------+------+----------------+----------+
25 rows in set (0.00 sec)You can also use GROUPING() in a HAVING clause to filter out only the roll‑up rows:
mysql> SELECT r1, r2, count(*) FROM y1 GROUP BY r1, r2 WITH ROLLUP HAVING GROUPING(r1) = 1 OR GROUPING(r2) = 1;
+------+------+----------+
| r1 | r2 | count(*) |
+------+------+----------+
| NULL | NULL | 1 |
| 1 | NULL | 4 |
| 2 | NULL | 6 |
| 3 | NULL | 3 |
| 4 | NULL | 9 |
| 5 | NULL | 3 |
| NULL | NULL | 26 |
+------+------+----------+
7 rows in set (0.00 sec)Summary
The article introduced MySQL 8.0's new GROUPING() function, showing how it can be used with GROUP BY ... WITH ROLLUP to clearly separate normal rows from roll‑up summary rows, and demonstrated its usage in SELECT lists, multiple‑column expressions, and HAVING clauses.
Aikesheng Open Source Community
The Aikesheng Open Source Community provides stable, enterprise‑grade MySQL open‑source tools and services, releases a premium open‑source component each year (1024), and continuously operates and maintains them.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.