Purpose
You can call this function to differentiate 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 types
This function returns data of the NUMBER data type.
Examples
Execute the following statements 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