Purpose
The GROUPING function differentiates between the NULL values generated by GROUP BY extensions (such as ROLLUP) and the NULL values in regular rows. It returns 1 for NULL values in the extended rows and 0 for 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 the NUMBER data type.
Examples
Create a table named tbl1 and insert data. Query aggregated rows and regular records that are grouped by the col2 and col3 columns and that include 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','Regular record'),COUNT(*)
FROM tbl1
GROUP BY ROLLUP(col2,col3);
+------+------+--------------------------------------------------+----------+
| COL2 | COL3 | DECODE(GROUPING(COL3),1,'Summary','Regular record') | COUNT(*) |
+------+------+--------------------------------------------------+----------+
| 1 | 6 | Regular record | 1 |
| 1 | 9 | Regular record | 1 |
| 1 | NULL | Regular record | 1 |
| 1 | NULL | Summary | 3 |
| 2 | 3 | Regular record | 1 |
| 2 | 9 | Regular record | 1 |
| 2 | 18 | Regular record | 1 |
| 2 | NULL | Regular record | 1 |
| 2 | NULL | Summary | 4 |
| 3 | 10 | Regular record | 1 |
| 3 | 12 | Regular record | 1 |
| 3 | NULL | Regular record | 1 |
| 3 | NULL | Summary | 3 |
| NULL | 5 | Regular record | 1 |
| NULL | NULL | Regular record | 1 |
| NULL | NULL | Summary | 2 |
| NULL | NULL | Summary | 12 |
+------+------+--------------------------------------------------+----------+
17 rows in set