GROUPING

GROUPING(expr [, expr] ...)

For GROUP BY queries that include a WITH ROLLUP modifier, the ROLLUP
operation produces super-aggregate output rows where NULL represents
the set of all values. The GROUPING() function enables you to
distinguish NULL values for super-aggregate rows from NULL values in
regular grouped rows.

GROUPING() is permitted only in the select list or HAVING clause.

Each argument to GROUPING() must be an expression that exactly matches
an expression in the GROUP BY clause. The expression cannot be a
positional specifier. For each expression, GROUPING() produces 1 if the
expression value in the current row is a NULL representing a
super-aggregate value. Otherwise, GROUPING() produces 0, indicating
that the expression value is a NULL for a regular result row or is not
NULL.

Suppose that table t1 contains these rows, where NULL indicates
something like "other" or "unknown":

mysql> SELECT * FROM t1;
+------+-------+----------+
| name | size | quantity |
+------+-------+----------+
| ball | small | 10 |
| ball | large | 20 |
| ball | NULL | 5 |
| hoop | small | 15 |
| hoop | large | 5 |
| hoop | NULL | 3 |
+------+-------+----------+

A summary of the table without WITH ROLLUP looks like this:

mysql> SELECT name, size, SUM(quantity) AS quantity
FROM t1
GROUP BY name, size;
+------+-------+----------+
| name | size | quantity |
+------+-------+----------+
| ball | small | 10 |
| ball | large | 20 |
| ball | NULL | 5 |
| hoop | small | 15 |
| hoop | large | 5 |
| hoop | NULL | 3 |
+------+-------+----------+

The result contains NULL values, but those do not represent
super-aggregate rows because the query does not include WITH ROLLUP.

Adding WITH ROLLUP produces super-aggregate summary rows containing
additional NULL values. However, without comparing this result to the
previous one, it is not easy to see which NULL values occur in
super-aggregate rows and which occur in regular grouped rows:

mysql> SELECT name, size, SUM(quantity) AS quantity
FROM t1
GROUP BY name, size WITH ROLLUP;
+------+-------+----------+
| name | size | quantity |
+------+-------+----------+
| ball | NULL | 5 |
| ball | large | 20 |
| ball | small | 10 |
| ball | NULL | 35 |
| hoop | NULL | 3 |
| hoop | large | 5 |
| hoop | small | 15 |
| hoop | NULL | 23 |
| NULL | NULL | 58 |
+------+-------+----------+

To distinguish NULL values in super-aggregate rows from those in
regular grouped rows, use GROUPING(), which returns 1 only for
super-aggregate NULL values:

mysql> SELECT
name, size, SUM(quantity) AS quantity,
GROUPING(name) AS grp_name,
GROUPING(size) AS grp_size
FROM t1
GROUP BY name, size WITH ROLLUP;
+------+-------+----------+----------+----------+
| name | size | quantity | grp_name | grp_size |
+------+-------+----------+----------+----------+
| ball | NULL | 5 | 0 | 0 |
| ball | large | 20 | 0 | 0 |
| ball | small | 10 | 0 | 0 |
| ball | NULL | 35 | 0 | 1 |
| hoop | NULL | 3 | 0 | 0 |
| hoop | large | 5 | 0 | 0 |
| hoop | small | 15 | 0 | 0 |
| hoop | NULL | 23 | 0 | 1 |
| NULL | NULL | 58 | 1 | 1 |
+------+-------+----------+----------+----------+

Common uses for GROUPING():

o Substitute a label for super-aggregate NULL values:

mysql> SELECT
IF(GROUPING(name) = 1, 'All items', name) AS name,
IF(GROUPING(size) = 1, 'All sizes', size) AS size,
SUM(quantity) AS quantity
FROM t1
GROUP BY name, size WITH ROLLUP;
+-----------+-----------+----------+
| name | size | quantity |
+-----------+-----------+----------+
| ball | NULL | 5 |
| ball | large | 20 |
| ball | small | 10 |
| ball | All sizes | 35 |
| hoop | NULL | 3 |
| hoop | large | 5 |
| hoop | small | 15 |
| hoop | All sizes | 23 |
| All items | All sizes | 58 |
+-----------+-----------+----------+

o Return only super-aggregate lines by filtering out the regular
grouped lines:

mysql> SELECT name, size, SUM(quantity) AS quantity
FROM t1
GROUP BY name, size WITH ROLLUP
HAVING GROUPING(name) = 1 OR GROUPING(size) = 1;
+------+------+----------+
| name | size | quantity |
+------+------+----------+
| ball | NULL | 35 |
| hoop | NULL | 23 |
| NULL | NULL | 58 |
+------+------+----------+

GROUPING() permits multiple expression arguments. In this case, the
GROUPING() return value represents a bitmask combined from the results
for each expression, where the lowest-order bit corresponds to the
result for the rightmost expression. For example, with three expression
arguments, GROUPING(expr1, expr2, expr3) is evaluated like this:

result for GROUPING(expr3)
+ result for GROUPING(expr2) << 1
+ result for GROUPING(expr1) << 2

The following query shows how GROUPING() results for single arguments
combine for a multiple-argument call to produce a bitmask value:

mysql> SELECT
name, size, SUM(quantity) AS quantity,
GROUPING(name) AS grp_name,
GROUPING(size) AS grp_size,
GROUPING(name, size) AS grp_all
FROM t1
GROUP BY name, size WITH ROLLUP;
+------+-------+----------+----------+----------+---------+
| name | size | quantity | grp_name | grp_size | grp_all |
+------+-------+----------+----------+----------+---------+
| ball | NULL | 5 | 0 | 0 | 0 |
| ball | large | 20 | 0 | 0 | 0 |
| ball | small | 10 | 0 | 0 | 0 |
| ball | NULL | 35 | 0 | 1 | 1 |
| hoop | NULL | 3 | 0 | 0 | 0 |
| hoop | large | 5 | 0 | 0 | 0 |
| hoop | small | 15 | 0 | 0 | 0 |
| hoop | NULL | 23 | 0 | 1 | 1 |
| NULL | NULL | 58 | 1 | 1 | 3 |
+------+-------+----------+----------+----------+---------+

With multiple expression arguments, the GROUPING() return value is
nonzero if any expression represents a super-aggregate value.
Multiple-argument GROUPING() syntax thus provides a simpler way to
write the earlier query that returned only super-aggregate rows, by
using a single multiple-argument GROUPING() call rather than multiple
single-argument calls:

mysql> SELECT name, size, SUM(quantity) AS quantity
FROM t1
GROUP BY name, size WITH ROLLUP
HAVING GROUPING(name, size) <> 0;
+------+------+----------+
| name | size | quantity |
+------+------+----------+
| ball | NULL | 35 |
| hoop | NULL | 23 |
| NULL | NULL | 58 |
+------+------+----------+

Use of GROUPING() is subject to these limitations:

o Do not use subquery GROUP BY expressions as GROUPING() arguments
because matching might fail. For example, matching fails for this
query:

mysql> SELECT GROUPING((SELECT MAX(name) FROM t1))
FROM t1
GROUP BY (SELECT MAX(name) FROM t1) WITH ROLLUP;
ERROR 3580 (HY000): Argument #1 of GROUPING function is not in GROUP BY

o GROUP BY literal expressions should not be used within a HAVING
clause as GROUPING() arguments. Due to differences between when the
optimizer evaluates GROUP BY and HAVING, matching may succeed but
GROUPING() evaluation does not produce the expected result. Consider
this query:

SELECT a AS f1, 'w' AS f2
FROM t
GROUP BY f1, f2 WITH ROLLUP
HAVING GROUPING(f2) = 1;

GROUPING() is evaluated earlier for the literal constant expression
than for the HAVING clause as a whole and returns 0. To check whether
a query such as this is affected, use EXPLAIN and look for Impossible
having in the Extra column.

URL: https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html