A ROLLUP extension generates a hierarchical set of groups with subtotals for each hierarchical group and a grand total. The order of the hierarchy is determined by the order of the expressions specified in the ROLLUP expression list. The top of the hierarchy is the leftmost item in the list. Each successive item proceeding to the right side moves down the hierarchy. The rightmost item is at the lowest level.

A single ROLLUP extension has the following syntax:

ROLLUP ( { expr_1 | ( expr_1a [, expr_1b ] ...) }
  [, expr_2 | ( expr_2a [, expr_2b ] ...) ] ...)

Each expr is an expression that determines the grouping of the result set. If enclosed within parentheses as ( expr_1a, expr_1b, ...), the combination of values returned by expr_1a and expr_1b defines a single grouping level of the hierarchy.

The base level of aggregates returned in the result set corresponds to each unique combination of values returned by the expression list.

A subtotal of each unique value is returned by the first item in the list. This item can be expr_1 or the combination of ( expr_1a, expr_1b, ...). A subtotal of each unique value is returned by the second item in the list. This item can be expr_2 or the combination of ( expr_2a, expr_2b, ...). Similar rules are used within each grouping of the first item and other items. Finally, a grand total is returned for the entire result set.

For the subtotal rows, null is returned for the items across which the subtotal is taken.

The following example shows the ROLLUP extension specified within the context of the GROUP BY clause:

SELECT select_list FROM ...
GROUP BY [... ,] ROLLUP ( expression_list ) [, ...]

The items specified in select_list must appear in the ROLLUP expression_list, be aggregate functions such as COUNT, SUM, AVG, MIN, or MAX, or be constants or functions such as the SYSDATE function whose returned values are independent of the individual rows in the group.

The GROUP BY clause may specify multiple ROLLUP extensions and multiple occurrences of other GROUP BY extensions and individual expressions.

You must use the ORDER BY clause if you want to display the output in a hierarchical or meaningful structure. The order of the result set is not determined if no ORDER BY clause is specified.

The number of grouping levels or totals is n + 1, where n represents the number of items in the ROLLUP expression list. A parenthesized list counts as one item.

The following query generates a rollup based on a hierarchy of columns loc, dname, and job.

SELECT loc, dname, job, COUNT(*) AS "employees" FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY ROLLUP (loc, dname, job)
ORDER BY 1, 2, 3;

The following example shows the result of this query. The system calculates the number of employees for each unique combination of loc, dname, and job, and also calculates the subtotals for each unique combination of loc and dname, for each unique value of loc, and a grand total displayed on the last line.

   loc    |   dname    |    job    | employees
----------+------------+-----------+-----------
 BOSTON   | OPERATIONS | ANALYST   |         1
 BOSTON   | OPERATIONS | CLERK     |         1
 BOSTON   | OPERATIONS | MANAGER   |         1
 BOSTON   | OPERATIONS |           |         3
 BOSTON   | RESEARCH   | ANALYST   |         2
 BOSTON   | RESEARCH   | CLERK     |         2
 BOSTON   | RESEARCH   | MANAGER   |         1
 BOSTON   | RESEARCH   |           |         5
 BOSTON   |            |           |         8
 CHICAGO  | SALES      | CLERK     |         1
 CHICAGO  | SALES      | MANAGER   |         1
 CHICAGO  | SALES      | SALESMAN  |         4
 CHICAGO  | SALES      |           |         6
 CHICAGO  |            |           |         6
 NEW YORK | ACCOUNTING | CLERK     |         1
 NEW YORK | ACCOUNTING | MANAGER   |         1
 NEW YORK | ACCOUNTING | PRESIDENT |         1
 NEW YORK | ACCOUNTING |           |         3
 NEW YORK |            |           |         3
          |            |           |        17
(20 rows)

The following query shows how to combine the items in the ROLLUP list within parentheses:

SELECT loc, dname, job, COUNT(*) AS "employees" FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY ROLLUP (loc, (dname, job))
ORDER BY 1, 2, 3;

In the following output, different from the last output, no subtotals are generated for loc and dname combinations.

   loc    |   dname    |    job    | employees
----------+------------+-----------+-----------
 BOSTON   | OPERATIONS | ANALYST   |         1
 BOSTON   | OPERATIONS | CLERK     |         1
 BOSTON   | OPERATIONS | MANAGER   |         1
 BOSTON   | RESEARCH   | ANALYST   |         2
 BOSTON   | RESEARCH   | CLERK     |         2
 BOSTON   | RESEARCH   | MANAGER   |         1
 BOSTON   |            |           |         8
 CHICAGO  | SALES      | CLERK     |         1
 CHICAGO  | SALES      | MANAGER   |         1
 CHICAGO  | SALES      | SALESMAN  |         4
 CHICAGO  |            |           |         6
 NEW YORK | ACCOUNTING | CLERK     |         1
 NEW YORK | ACCOUNTING | MANAGER   |         1
 NEW YORK | ACCOUNTING | PRESIDENT |         1
 NEW YORK |            |           |         3
          |            |           |        17
(16 rows)

If the first two columns in the ROLLUP list are enclosed in parentheses, the subtotal levels are different.

SELECT loc, dname, job, COUNT(*) AS "employees" FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY ROLLUP ((loc, dname), job)
ORDER BY 1, 2, 3;

A subtotal is generated for each unique loc and dname combination, but none for unique values of loc.

   loc    |   dname    |    job    | employees
----------+------------+-----------+-----------
 BOSTON   | OPERATIONS | ANALYST   |         1
 BOSTON   | OPERATIONS | CLERK     |         1
 BOSTON   | OPERATIONS | MANAGER   |         1
 BOSTON   | OPERATIONS |           |         3
 BOSTON   | RESEARCH   | ANALYST   |         2
 BOSTON   | RESEARCH   | CLERK     |         2
 BOSTON   | RESEARCH   | MANAGER   |         1
 BOSTON   | RESEARCH   |           |         5
 CHICAGO  | SALES      | CLERK     |         1
 CHICAGO  | SALES      | MANAGER   |         1
 CHICAGO  | SALES      | SALESMAN  |         4
 CHICAGO  | SALES      |           |         6
 NEW YORK | ACCOUNTING | CLERK     |         1
 NEW YORK | ACCOUNTING | MANAGER   |         1
 NEW YORK | ACCOUNTING | PRESIDENT |         1
 NEW YORK | ACCOUNTING |           |         3
          |            |           |        17
(17 rows)