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.