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 that are specified in the ROLLUP expression list. The top of the hierarchy is the leftmost item in the list. Each successive item that proceeds to the right side moves down the hierarchy. The rightmost item is at the lowest level.
Syntax
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 the expressions are 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 that are returned by the expression list.
- A subtotal is returned for each unique value of the first item in the list. This item can be expr_1 or the combination of (expr_1a, expr_1b, ...). A subtotal is returned for each unique value of 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 level 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.
Examples
The following example shows a ROLLUP extension that is specified within the GROUP BY clause:
SELECT select_list FROM ...
GROUP BY [... ,] ROLLUP ( expression_list ) [, ...]
- The items specified in select_list must meet one of the following requirements: they must appear in the ROLLUP expression_list, they must be aggregate functions such as COUNT, SUM, AVG, MIN, or MAX, or they must be constants or functions whose return values are independent of the individual rows in the group, such as the SYSDATE function.
- The GROUP BY clause may specify multiple ROLLUP extensions and multiple occurrences of other GROUP BY extensions and individual expressions.
- If you want to display the output in a hierarchical or meaningful structure, you must use the ORDER BY clause. If no ORDER BY clause is specified, the order of the result set is not determined.
- The number of grouping levels or totals is N + 1. 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 the loc, dname, and job columns:
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 output shows the result of the query. The system calculates the number of employees for each unique combination of loc, dname, and job and calculates the subtotals for each unique combination of loc and dname and each unique value of loc. Then, the system generates a grand total that is 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;
The following output shows the result of the query. The result is different from the preceding result in that 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 within 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 no subtotal is generated 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)