Purpose
You can call this function to return a subtotal for values of each data group and a total for values of all groups. It is an aggregate function. 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_names, it is equivalent to the UNION combination of N+1 GROUP BY groups.
Examples
Execute the following statement to create table 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
