All Products
Search
Document Center

ROLLUP

Last Updated: Jun 18, 2021

The ROLLUP function is an aggregate function. It is a simple extension to the GROUP BY statement. When data statistics and reports are being generated, the function returns a subtotal for each group and a grand total for all the groups. This function is more efficient than the combination of GROUP BY and UNION.

The ROLLUP function runs in a simple way. The function runs in the following sequence:

  • Group data from right to left in descending order based on the column that is specified by the parameter.

  • Calculate a subtotal for each group, and then calculate a grand total for all the groups.

  • Sort the data by ORDER BY col1 (,col2,col3,col4 ...).

If the number of parameters in ROLLUP is N, the result of this function is equivalent to UNION of N+1 GROUP BY groups.

The ROLLUP function makes tasks that involve group statistics efficient. For example, to calculate subtotals along a hierarchical dimension, such as time or geography, you need only to use ROLLUP(y, m, day) or ROLLUP(country, state, city) for the query. Data warehouse administrators can simplify and speed up the maintenance of aggregate tables by using the ROLLUP function.

Syntax

SELECT ... GROUP BY ROLLUP(col1 [,col2...])

Parameters

Parameter

Description

col1

The name of the column by which data is grouped. The number of columns refers to the number of rows in the database.

Examples

To create the group_test table and insert data into the table, execute the following statements:

CREATE TABLE group_test (group_id int, job varchar2(10), name varchar2(10), salary int);
INSERT INTO group_test VALUES (10, 'Coding', 'Bruce', 1000);
INSERT INTO group_test VALUES (10, 'Programmer', 'Clair', 1000);
INSERT INTO group_test VALUES (20, 'Coding', 'Jason', 2000);
INSERT INTO group_test VALUES (20, 'Programmer', 'Joey', 2000);
INSERT INTO group_test VALUES (30, 'Coding', 'Rebecca', 3000);
INSERT INTO group_test VALUES (30, 'Programmer', 'Rex', 3000);
INSERT INTO group_test VALUES (40, 'Coding', 'Samuel', 4000);
INSERT INTO group_test VALUES (40, 'Programmer', 'Susy', 4000);
COMMIT;

To use GROUP BY to group data by group_id, execute the following statement:

SELECT group_id, SUM(salary) FROM group_test GROUP BY group_id;

The following query result is returned:

+----------+-------------+
| GROUP_ID | SUM(SALARY) |
+----------+-------------+
|       10 |        2000 |
|       20 |        4000 |
|       30 |        6000 |
|       40 |        8000 |
+----------+-------------+

To use the ROLLUP function to group data by group_id and calculate a grand total, execute the following statement:

SELECT group_id, SUM(salary) FROM group_test GROUP BY ROLLUP (group_id);

The following query result is returned:

+----------+-------------+
| GROUP_ID | SUM(SALARY) |
+----------+-------------+
|       10 |        2000 |
|       20 |        4000 |
|       30 |        6000 |
|       40 |        8000 |
|     NULL |       20000 |
+----------+-------------+

To use the ROLLUP function to group data by the group_id and job columns and calculate a grand total, execute the following statement:

SELECT group_id, job, SUM(salary) FROM group_test GROUP BY ROLLUP (group_id, job);

The following query result is returned:

+----------+------------+-------------+
| 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 |
+----------+------------+-------------+

To replace the preceding SQL statement with the combination of GROUP BY and UNION, execute the following statement:

SELECT group_id, job, SUM(salary) FROM group_test GROUP BY group_id, job
UNION ALL
SELECT group_id, NULL, SUM(salary) FROM group_test GROUP BY group_id
UNION ALL
SELECT NULL, NULL, SUM(salary) FROM group_test ORDER BY 1, 2;

The following query result is returned:

+----------+------------+-------------+
| 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 |
+----------+------------+-------------+

The output result is the same as that of the ROLLUP function. However, the ROLLUP function is simpler and more efficient.