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 in the select list, HAVING clause, and (as of
MySQL 8.0.12) ORDER BY 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

