Purpose
ROLLUP() is an aggregate function. It returns a subtotal for values of each data group and a total for values of all groups. It is a simple extension of the GROUP BY statement. Compared with the combination of GROUP BY and UNION, this function offers higher efficiency in statistical data analysis and report generation. The ROLLUP function is very efficient in tasks involving statistical data grouping. For example, to obtain subtotals by time or geography, you can query ROLLUP(y, m, day) or ROLLUP(country, state, city). You can simplify and speed up the maintenance of aggregate tables by using ROLLUP functions.
The process for executing the ROLLUP function is divided into the following steps:
Group data by the column specified by the parameter in descending order from right to left.
Calculate the subtotal for each group and total for all groups.
Sort data as per
ORDER BY col1 (,col2,col3,col4 ...).
Syntax
SELECT ... GROUP BY ROLLUP(col_name [,col_name...])
Parameters
col_name specifies the column for grouping. If the ROLLUP parameter is specified with N col_name values, it is equivalent to the UNION combination of N+1 GROUP BY groups.
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
Execute GROUP BY on group_id and obtain 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
Group data by group_id by using the ROLLUP function and obtain the sum of salary simultaneously.
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
Group data by the group_id and job column by using the ROLLUP function and obtain the sum of salary simultaneously.
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
You can replace the preceding statement with the combination of GROUP BY and UNION ALL. The result is the same as that of the ROLLUP function. However, the ROLLUP function is simpler and more 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