Starting with MySQL 8.0.1, the server supports the SQL GROUPING function. The GROUPING function is used to distinguish between a NULL representing the set of all values in a super-aggregate row (produced by a ROLLUP operation) from a NULL in a regular row.
Introduction
MySQL server has supported GROUP BY extension ROLLUP for sometime now. Here is an example of how to use ROLLUP with GROUP BY.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
|
mysql> create table t1 (a integer, b integer, c integer); Query OK, 0 rows affected (0.05 sec) mysql> insert into t1 values (111,11,11); Query OK, 1 row affected (0.01 sec) mysql> insert into t1 values (222,22,22); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values (111,12,12); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values (222,23,23); Query OK, 1 row affected (0.01 sec) mysql> select * from t1; +------+------+------+ | a | b | c | +------+------+------+ | 111 | 11 | 11 | | 222 | 22 | 22 | | 111 | 12 | 12 | | 222 | 23 | 23 | +------+------+------+ 4 rows in set (0.00 sec) mysql> SELECT a, b, SUM(c) as SUM FROM t1 GROUP BY a,b WITH ROLLUP; +------+------+------+ | a | b | SUM | +------+------+------+ | 111 | 11 | 11 | | 111 | 12 | 12 | | 111 | NULL | 23 | | 222 | 22 | 22 | | 222 | 23 | 23 | | 222 | NULL | 45 | | NULL | NULL | 68 | +------+------+------+ 7 rows in set (0.00 sec) |
As we see in the above result, NULL’s are added by the ROLLUP modifier for every super aggregate row.
Now let us add NULL’s into the table data:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
mysql> INSERT INTO t1 values (1111,NULL,112); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO t1 values (NULL,112,NULL); Query OK, 1 row affected (0.00 sec) mysql> select * from t1; +------+------+------+ | a | b | c | +------+------+------+ | 111 | 11 | 11 | | 222 | 22 | 22 | | 111 | 12 | 12 | | 222 | 23 | 23 | | 1111 | NULL | 112 | | NULL | 112 | NULL | +------+------+------+ 6 rows in set (0.00 sec) |
We have the following result when we query the data with ROLLUP after the addition of NULL’s into table data.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
mysql> SELECT a, b, SUM(c) as SUM FROM t1 GROUP BY a,b WITH ROLLUP; +------+------+------+ | a | b | SUM | +------+------+------+ | NULL | 112 | NULL | | NULL | NULL | NULL | | 111 | 11 | 11 | | 111 | 12 | 12 | | 111 | NULL | 23 | | 222 | 22 | 22 | | 222 | 23 | 23 | | 222 | NULL | 45 | | 1111 | NULL | 112 | | 1111 | NULL | 112 | | NULL | NULL | 180 | +------+------+------+ 11 rows in set (0.01 sec) |
As we can see in the above example, it is now difficult to distinguish whether a NULL is representing a regular grouped value or a super-aggregate value.
What is new in MySQL-8.0.1
The GROUPING function can be used in the above example to differentiate NULLs produced by ROLLUP from NULLs from the grouped data. GROUPING function for a column returns a value of 1 when the NULL generated for that column is a result of ROLLUP operation. Else it returns a value of 0.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
mysql> SELECT a, b, SUM(c) as SUM, GROUPING(a), GROUPING(b) FROM t1 GROUP BY a,b WITH ROLLUP; +------+------+------+-------------+-------------+ | a | b | SUM | GROUPING(a) | GROUPING(b) | +------+------+------+-------------+-------------+ | NULL | 112 | NULL | 0 | 0 | | NULL | NULL | NULL | 0 | 1 | | 111 | 11 | 11 | 0 | 0 | | 111 | 12 | 12 | 0 | 0 | | 111 | NULL | 23 | 0 | 1 | | 222 | 22 | 22 | 0 | 0 | | 222 | 23 | 23 | 0 | 0 | | 222 | NULL | 45 | 0 | 1 | | 1111 | NULL | 112 | 0 | 0 | | 1111 | NULL | 112 | 0 | 1 | | NULL | NULL | 180 | 1 | 1 | +------+------+------+-------------+-------------+ 11 rows in set (0.01 sec) |
As we see in the above example, GROUPING(b) returns a value of 1 only for those rows which have NULLs produced by a ROLLUP operation.
Another way of using the GROUPING function is by passing multiple columns as arguments to a single GROUPING function. The result of the GROUPING function would then be an integer bit mask having 1’s for the arguments which have GROUPING(argument) as 1.
For example:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
mysql> SELECT a, b, SUM(c) as SUM, GROUPING(a,b) FROM t1 GROUP BY a,b WITH ROLLUP; +------+------+------+---------------+ | a | b | SUM | GROUPING(a,b) | +------+------+------+---------------+ | NULL | 112 | NULL | 0 | | NULL | NULL | NULL | 1 | | 111 | 11 | 11 | 0 | | 111 | 12 | 12 | 0 | | 111 | NULL | 23 | 1 | | 222 | 22 | 22 | 0 | | 222 | 23 | 23 | 0 | | 222 | NULL | 45 | 1 | | 1111 | NULL | 112 | 0 | | 1111 | NULL | 112 | 1 | | NULL | NULL | 180 | 3 | +------+------+------+---------------+ 11 rows in set (0.00 sec) |
As seen here, if GROUPING (a,b) returns 3, it means that NULL in column “a” and NULL in column “b” for that row is produce by a ROLLUP operation. If result is 1, NULL in column “b” alone is a result of ROLLUP operation.
Other uses of GROUPING function
We can specify GROUPING function in a select list or in a having condition. When specified in having condition, we can use this function to retrieve only super-aggregate rows or only aggregate rows like in the example below.
|
1
2
3
4
5
6
7
8
9
10
11
|
mysql> SELECT a, b, SUM(c) as SUM FROM t1 GROUP BY a,b WITH ROLLUP HAVING GROUPING(a) = 1 or GROUPING(b) = 1; +------+------+------+ | a | b | SUM | +------+------+------+ | NULL | NULL | NULL | | 111 | NULL | 23 | | 222 | NULL | 45 | | 1111 | NULL | 112 | | NULL | NULL | 180 | +------+------+------+ 5 rows in set (0.00 sec) |
We can also use GROUPING function to differentiate super aggregates from aggregates in a nice way as shown below:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
mysql> SELECT IF(GROUPING(a)=1,'All Departments', a) as Department, IF(GROUPING(b)=1, 'All Employees', b) as Employees, SUM(c) as SUM FROM t1 GROUP BY a,b WITH ROLLUP; +-----------------+---------------+------+ | Department | Employees | SUM | +-----------------+---------------+------+ | NULL | 112 | NULL | | NULL | All Employees | NULL | | 111 | 11 | 11 | | 111 | 12 | 12 | | 111 | All Employees | 23 | | 222 | 22 | 22 | | 222 | 23 | 23 | | 222 | All Employees | 45 | | 1111 | NULL | 112 | | 1111 | All Employees | 112 | | All Departments | All Employees | 180 | +-----------------+---------------+------+ 11 rows in set (0.00 sec) |
Those were some of the uses of newly added GROUPING function in MySQL server.
Conclusion
We would like to thank Zhe Dong for his contribution, which we used as the basis of this feature addition. Please try out GROUPING, and let us know your feedback 🙂