Purpose
This function distinguishes NULL values in summary rows generated by an extension clause of GROUP BY, such as ROLLUP, from NULL values in regular rows. It returns 1 for NULL values in summary rows and 0 for regular rows.
Syntax
GROUPING(expr)
Parameters
Parameter expr specifies an expression in the GROUP BY clause.
Return type
NUMBER
Examples
Create table tbl1 and insert sample data. Query the data grouped by columns col2 and col3 with a ROLLUP clause to retrieve the summary rows and regular rows.
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