Purpose
This function distinguishes between NULL values from GROUP BY subclauses that extend (such as ROLLUP) and regular NULL values in rows. It returns 1 for NULL values from a ROLLUP-like extension and 0 for values in regular rows.
Syntax
GROUPING(expr)
Parameters
The expr parameter is an expression in the GROUP BY subclause.
Return type
Returns the NUMBER data type.
Examples
Create table tbl1 and insert data. Query to group by column col2 and column col3, as well as the ROLLUP subclause, to generate summary rows and regular records.
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