SQL - GROUPING() Function

Aggregation is a collection of an objects that are bound together as a single entity. The SQL GROUPING() function is used to verify whether a column expression in a group by clause is aggregated or not. This function returns 1 if the given column expression is aggregated and 0, if it is not.

This function is used to differentiate between a NULL in a regular row and a NULL signifying the set of all values in a super-aggregate row (generated by a ROLLUP operation).

Grouping can be only used with the SELECT, LIST, HAVING, ORDER BY clause when GROUP BY is specified.

Syntax

Following is the syntax of SQL GROUPING() function −

 GROUPING(column_expression); 

Parameters

• column_expression − It is a column or expression that contains a column in a GROUP BY clause.

Example

Assume we have created a table named customer using the following query −

 CREATE TABLE customers(ID INT NOT NULL, NAME VARCHAR(30) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(30), SALARY DECIMAL(18, 2) ); 

The table stores the ID, NAME, AGE, ADDRESS, and SALARY. Now we are inserting the 7 records in the customers table using the INSERT statement.

 INSERT INTO customers VALUES(1, 'Ramesh', 32, 'Ahmedabad', 2000.00); INSERT INTO customers VALUES(2, 'Khilan', 25, 'Delhi', 1500.00); INSERT INTO customers VALUES(3, 'kaushik', 23, 'Kota', 2000.00); INSERT INTO customers VALUES(4, 'Chaitali', 25, 'Mumbai', 6500.00); INSERT INTO customers VALUES(5, 'Hardik', 27, 'Bhopal', 8500.00); INSERT INTO customers VALUES(6, 'Komal', 22, 'MP', 4500.00); INSERT INTO customers VALUES(7, 'Aman', 23, 'Ranchi', null); 

The customers table table will be as follows −

 +----+----------+-----+-----------+---------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+---------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Aman | 23 | Ranchi | NULL | +----+----------+-----+-----------+---------+ 

Following is the query, which uses the grouping function to group by age and aggregate salary amount −

 SELECT AGE, SUM(SALARY) as SALARY_SUM, GROUPING(AGE) AS 'GROUPING' FROM customers GROUP BY AGE WITH ROLLUP; 

Output

Following is the output of the above SQL query, which shows one null value under the AGE. The null is the summary row added by the ROLLUP operation. The summary rows show the salary sum amount for all age groups, which is indicated by 1 in the GROUPING column −

 +------+------------+----------+ | AGE | SALARY_SUM | GROUPING | +------+------------+----------+ | 22 | 4500.00 | 0 | | 23 | 2000.00 | 0 | | 25 | 8000.00 | 0 | | 27 | 8500.00 | 0 | | 32 | 2000.00 | 0 | | NULL | 25000.00 | 1 | +------+------------+----------+ 

Example

We can use the grouping function in a select or in a having clause. When the having clause is specified, then we can use the grouping function to only retrieve the super-aggregate rows or only the aggregate rows, following is an example −

 SELECT AGE, SALARY, SUM(SALARY) as SALARY_SUM FROM customers GROUP BY AGE, SALARY WITH ROLLUP HAVING GROUPING(AGE) = 1 or GROUPING(SALARY) = 1; 

Output

Following is the output of the above SQL query −

 +------+--------+------------+ | AGE | SALARY | SALARY_SUM | +------+--------+------------+ | 22 | NULL | 4500.00 | | 23 | NULL | 2000.00 | | 25 | NULL | 8000.00 | | 27 | NULL | 8500.00 | | 32 | NULL | 2000.00 | | NULL | NULL | 25000.00 | +------+--------+------------+ 

Example

In the following example, we are using the grouping() function for two columns. The grouping function for a column returns a value of 1 when the null generated for that column is a result of a rollup operation. Otherwise, it returns a value of 0.

 SELECT ID, AGE, SUM(SALARY) as SUM, GROUPING(ID), GROUPING(AGE) FROM customers GROUP BY ID, AGE WITH ROLLUP; 

Output

Following is the output of the above SQL query −

 +------+------+----------+--------------+---------------+ | ID | AGE | SUM | GROUPING(ID) | GROUPING(AGE) | +------+------+----------+--------------+---------------+ | 1 | 32 | 2000.00 | 0 | 0 | | 1 | NULL | 2000.00 | 0 | 1 | | 2 | 25 | 1500.00 | 0 | 0 | | 2 | NULL | 1500.00 | 0 | 1 | | 3 | 23 | 2000.00 | 0 | 0 | | 3 | NULL | 2000.00 | 0 | 1 | | 4 | 25 | 6500.00 | 0 | 0 | | 4 | NULL | 6500.00 | 0 | 1 | | 5 | 27 | 8500.00 | 0 | 0 | | 5 | NULL | 8500.00 | 0 | 1 | | 6 | 22 | 4500.00 | 0 | 0 | | 6 | NULL | 4500.00 | 0 | 1 | | 7 | 23 | NULL | 0 | 0 | | 7 | NULL | NULL | 0 | 1 | | NULL | NULL | 25000.00 | 1 | 1 | +------+------+----------+--------------+---------------+