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
Execute the following statement to create table 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