Purpose
GROUPING() differentiates NULL values in super-aggregate rows generated by an extension of the GROUP BY clause (such as ROLLUP) from NULL values in regular rows. It returns 1 for NULL values in super-aggregate rows and returns 0 for NULL values in regular rows.
Syntax
GROUPING(expr)
Parameters
The expr parameter is an expression in the GROUP BY clause.
Return type
The return type is NUMBER.
Examples
Create a table named tbl1 and insert data into it. Group the data by the col2 and col3 columns. Query the summary rows and normal records generated by the ROLLUP clause.
obclient> CREATE TABLE tbl1(col1 INT,col2 INT,col3 INT);
Query OK, 0 rows affected
obclient> INSERT INTO tbl1 VALUES(1,NULL,NULL),(2,NULL,5),(3,1,6),(4,1,9),
(5,1,NULL),(6,2,3),(7,2,NULL),(8,2,18),(9,2,9),
(10,3,10),(11,3,12),(12,3,NULL);
Query OK, 12 rows affected
Records: 12 Duplicates: 0 Warnings: 0
obclient> SELECT * FROM tbl1;
+------+------+------+
| COL1 | COL2 | COL3 |
+------+------+------+
| 1 | NULL | NULL |
| 2 | NULL | 5 |
| 3 | 1 | 6 |
| 4 | 1 | 9 |
| 5 | 1 | NULL |
| 6 | 2 | 3 |
| 7 | 2 | NULL |
| 8 | 2 | 18 |
| 9 | 2 | 9 |
| 10 | 3 | 10 |
| 11 | 3 | 12 |
| 12 | 3 | NULL |
+------+------+------+
12 rows in set
obclient> SELECT col2,col3,DECODE(GROUPING(col3),1,'Summary','Normal record'),COUNT(*)
FROM tbl1
GROUP BY ROLLUP(col2,col3);
+------+------+--------------------------------------------------+----------+
| COL2 | COL3 | DECODE(GROUPING(COL3),1,'Summary','Normal record') | COUNT(*) |
+------+------+--------------------------------------------------+----------+
| 1 | 6 | Normal record | 1 |
| 1 | 9 | Normal record | 1 |
| 1 | NULL | Normal record | 1 |
| 1 | NULL | Summary | 3 |
| 2 | 3 | Normal record | 1 |
| 2 | 9 | Normal record | 1 |
| 2 | 18 | Normal record | 1 |
| 2 | NULL | Normal record | 1 |
| 2 | NULL | Summary | 4 |
| 3 | 10 | Normal record | 1 |
| 3 | 12 | Normal record | 1 |
| 3 | NULL | Normal record | 1 |
| 3 | NULL | Summary | 3 |
| NULL | 5 | Normal record | 1 |
| NULL | NULL | Normal record | 1 |
| NULL | NULL | Summary | 2 |
| NULL | NULL | Summary | 12 |
+------+------+--------------------------------------------------+----------+
17 rows in set
