Purpose
This function differentiates between the NULL values of the super aggregated rows generated by GROUP BY clause extensions such as ROLLUP, and the NULL values of normal rows. For the NULL values of the super aggregated rows, this function returns 1, and for the values of the normal rows, it returns 0.
Syntax
GROUPING(expr)
Parameters
The expr parameter is an expression in the GROUP BY clause.
Return type
NUMBER.
Examples
Create a table tbl1 and insert data into the table. Query data from the table grouped by col2 and col3 and the aggregated rows 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,'Aggregated','Normal rows'),COUNT(*)
FROM tbl1
GROUP BY ROLLUP(col2,col3);
+------+------+--------------------------------------------------+----------+
| COL2 | COL3 | DECODE(GROUPING(COL3),1,'Aggregated','Normal rows') | COUNT(*) |
+------+------+--------------------------------------------------+----------+
| 1 | 6 | Normal rows | 1 |
| 1 | 9 | Normal rows | 1 |
| 1 | NULL | Normal rows | 1 |
| 1 | NULL | Aggregated | 3 |
| 2 | 3 | Normal rows | 1 |
| 2 | 9 | Normal rows | 1 |
| 2 | 18 | Normal rows | 1 |
| 2 | NULL | Normal rows | 1 |
| 2 | NULL | Aggregated | 4 |
| 3 | 10 | Normal rows | 1 |
| 3 | 12 | Normal rows | 1 |
| 3 | NULL | Normal rows | 1 |
| 3 | NULL | Aggregated | 3 |
| NULL | 5 | Normal rows | 1 |
| NULL | NULL | Normal rows | 1 |
| NULL | NULL | Aggregated | 2 |
| NULL | NULL | Aggregated | 12 |
+------+------+--------------------------------------------------+----------+
17 rows in set