Purpose
This function is an aggregate function that can return subtotals for each group and a grand total for all groups. It is a simple extension of the GROUP BY statement and is more efficient than using GROUP BY in combination with UNION in data statistics and report generation. The ROLLUP function is highly efficient for tasks involving group statistics. For example, to get subtotals along hierarchical dimensions such as time or geography, you can query ROLLUP(y, m, day) or ROLLUP(country, state, city). Data warehouse administrators can use the ROLLUP function to simplify and accelerate the maintenance of summary tables.
The execution process of the ROLLUP function is as follows:
Group the data from right to left based on the specified columns.
Calculate subtotals for each group and then calculate the grand total for all groups.
Sort the results according to
ORDER BY col1 (, col2, col3, col4 ...).
Syntax
SELECT ... GROUP BY ROLLUP(col_name [,col_name...])
Parameters
col_name specifies the column to be grouped. If the ROLLUP function has N col_name parameters, it is equivalent to combining N+1 GROUP BY groups using UNION.
Examples
Create a table named group_tbl1 and insert data into it.
obclient> CREATE TABLE group_tbl1 (group_id INT,job VARCHAR2(10),name VARCHAR2(10),salary INT);
Query OK, 0 rows affected
obclient> INSERT INTO group_tbl1 VALUES
(10,'Coding','Bruce',1000),
(10,'Programmer','Clair',1000),
(20,'Coding','Jason',2000),
(20,'Programmer','Joey',2000),
(30,'Coding','Rebecca',3000),
(30,'Programmer','Rex',3000),
(40,'Coding','Samuel',4000),
(40,'Programmer','Susy',4000);
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0
obclient> SELECT * FROM group_tbl1;
+----------+------------+---------+--------+
| GROUP_ID | JOB | NAME | SALARY |
+----------+------------+---------+--------+
| 10 | Coding | Bruce | 1000 |
| 10 | Programmer | Clair | 1000 |
| 20 | Coding | Jason | 2000 |
| 20 | Programmer | Joey | 2000 |
| 30 | Coding | Rebecca | 3000 |
| 30 | Programmer | Rex | 3000 |
| 40 | Coding | Samuel | 4000 |
| 40 | Programmer | Susy | 4000 |
+----------+------------+---------+--------+
8 rows in set
Perform a GROUP BY operation on group_id and calculate the sum of salary.
obclient> SELECT group_id,SUM(salary) FROM group_tbl1 GROUP BY group_id;
+----------+-------------+
| GROUP_ID | SUM(SALARY) |
+----------+-------------+
| 10 | 2000 |
| 20 | 4000 |
| 30 | 6000 |
| 40 | 8000 |
+----------+-------------+
4 rows in set
Use the ROLLUP function to group by group_id and calculate the sum of salary.
obclient> SELECT group_id,SUM(salary) FROM group_tbl1 GROUP BY ROLLUP (group_id);
+----------+-------------+
| GROUP_ID | SUM(SALARY) |
+----------+-------------+
| 10 | 2000 |
| 20 | 4000 |
| 30 | 6000 |
| 40 | 8000 |
| NULL | 20000 |
+----------+-------------+
5 rows in set
Use the ROLLUP function to group by group_id and job, and calculate the sum of salary.
obclient> SELECT group_id,job,SUM(salary) FROM group_tbl1 GROUP BY ROLLUP (group_id,job);
+----------+------------+-------------+
| GROUP_ID | JOB | SUM(SALARY) |
+----------+------------+-------------+
| 10 | Coding | 1000 |
| 10 | Programmer | 1000 |
| 10 | NULL | 2000 |
| 20 | Coding | 2000 |
| 20 | Programmer | 2000 |
| 20 | NULL | 4000 |
| 30 | Coding | 3000 |
| 30 | Programmer | 3000 |
| 30 | NULL | 6000 |
| 40 | Coding | 4000 |
| 40 | Programmer | 4000 |
| 40 | NULL | 8000 |
| NULL | NULL | 20000 |
+----------+------------+-------------+
13 rows in set
The above SQL statement can be replaced with a combination of GROUP BY and UNION ALL, producing the same output as the ROLLUP function, but the ROLLUP function is more concise and efficient.
obclient> SELECT group_id,job,SUM(salary) FROM group_tbl1 GROUP BY group_id, job
UNION ALL
SELECT group_id,NULL,SUM(salary) FROM group_tbl1 GROUP BY group_id
UNION ALL
SELECT NULL,NULL,SUM(salary) FROM group_tbl1 ORDER BY 1,2;
+----------+------------+-------------+
| GROUP_ID | JOB | SUM(SALARY) |
+----------+------------+-------------+
| 10 | Coding | 1000 |
| 10 | Programmer | 1000 |
| 10 | NULL | 2000 |
| 20 | Coding | 2000 |
| 20 | Programmer | 2000 |
| 20 | NULL | 4000 |
| 30 | Coding | 3000 |
| 30 | Programmer | 3000 |
| 30 | NULL | 6000 |
| 40 | Coding | 4000 |
| 40 | Programmer | 4000 |
| 40 | NULL | 8000 |
| NULL | NULL | 20000 |
+----------+------------+-------------+
13 rows in set