Syntax
GROUPING(expr)
Notes
When you execute the GROUP BY statement, you can specify the WITH ROLLUP clause to aggregate each group. This function performs a higher-level aggregation operation, which is also known as super-aggregate, and generates additional rows. For example, you can execute the following SQL statement to group the sales profit by year and generate the sum of each group.
The row whose year value is NULL is super-aggregated:
obclient> SELECT year, SUM(profit) AS profit
FROM sales
GROUP BY year WITH ROLLUP;
+------+--------+
year profit
+------+--------+
2000 4525
2001 3010
NULL 7535
+------+--------+
If you use WITH ROLLUP to modify multiple GROUP BY columns, the result is more complex. In this case, each group produces a super-aggregated row. The following sample SQL statement generates additional super-aggregated rows:
obclient> SELECT year, country, product, SUM(profit) AS profit
FROM sales
GROUP BY year, country, product WITH ROLLUP;
+------+---------+------------+--------+
year country product profit
+------+---------+------------+--------+
2000 Finland Computer 1500
2000 Finland Phone 100
2000 Finland NULL 1600
2000 India Calculator 150
2000 India Computer 1200
2000 India NULL 1350
2000 USA Calculator 75
2000 USA Computer 1500
2000 USA NULL 1575
2000 NULL NULL 4525
2001 Finland Phone 10
2001 Finland NULL 10
2001 USA Calculator 50
2001 USA Computer 2700
2001 USA TV 250
2001 USA NULL 3000
2001 NULL NULL 3010
NULL NULL NULL 7535
+------+---------+------------+--------+
The GROUPING function identifies the super-aggregated rows. If the row is super-aggregated, the function returns 1. Otherwise, it returns 0.
For example, you can use the GROUPING function to rewrite the preceding sample SQL statement:
obclient> SELECT
IF(GROUPING(year), 'All years', year) AS year,
IF(GROUPING(country), 'All countries', country) AS country,
IF(GROUPING(product), 'All products', product) AS product,
SUM(profit) AS profit
FROM sales
GROUP BY year, country, product WITH ROLLUP;
+-----------+---------------+--------------+--------+
year country product profit
+-----------+---------------+--------------+--------+
2000 Finland Computer 1500
2000 Finland Phone 100
2000 Finland All products 1600
2000 India Calculator 150
2000 India Computer 1200
2000 India All products 1350
2000 USA Calculator 75
2000 USA Computer 1500
2000 USA All products 1575
2000 All countries All products 4525
2001 Finland Phone 10
2001 Finland All products 10
2001 USA Calculator 50
2001 USA Computer 2700
2001 USA TV 250
2001 USA All products 3000
2001 All countries All products 3010
All years All countries All products 7535
+-----------+---------------+--------------+--------+
Examples
obclient> CREATE TABLE employees (manager_id INT,last_name varchar(50),hiredate varchar(50),SALARY INT);
INSERT INTO employees VALUES(100, 'Raphaely', '2017-07-01', 1700);
INSERT INTO employees VALUES(100, 'De Haan', '2018-05-01',11000);
INSERT INTO employees VALUES(100, 'Errazuriz', '2017-07-21', 1400);
INSERT INTO employees VALUES(100, 'Hartstein', '2019-05-01',14000);
INSERT INTO employees VALUES(100, 'Raphaely', '2017-07-22', 1700);
INSERT INTO employees VALUES(100, 'Weiss', '2019-07-11',13500);
INSERT INTO employees VALUES(100, 'Russell', '2019-10-05', 13000);
INSERT INTO employees VALUES(100, 'Partners', '2018-12-01',14000);
INSERT INTO employees VALUES(200, 'Ross', '2019-06-11',13500);
INSERT INTO employees VALUES(200, 'Bell', '2019-05-25', 13000);
INSERT INTO employees VALUES(200, 'Part', '2018-08-11',14000);
COMMIT;
obclient> SELECT IF(GROUPING(manager_id),'manager_id',manager_id) AS manager_id,SUM(SALARY) FROM employees GROUP BY manager_id WITH ROLLUP;
+------------+-------------+
manager_id SUM(SALARY)
+------------+-------------+
100 70300
200 40500
manager_id 110800
+------------+-------------+
3 rows in set (0.00 sec)