ROLLUP extension v17
The ROLLUP extension produces a hierarchical set of groups with subtotals for each hierarchical group as well as a grand total. The order of the hierarchy is determined by the order of the expressions given in the ROLLUP expression list. The top of the hierarchy is the left-most item in the list. Each successive item proceeding to the right moves down the hierarchy. The right-most item is at the lowest level.
Syntax
The syntax for a single ROLLUP is:
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 in parenthesis as ( expr_1a, expr_1b, ...), then 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 is for each unique combination of values returned by the expression list.
In addition, a subtotal is returned for the first item in the list (expr_1 or the combination of ( expr_1a, expr_1b, ...), whichever is specified) for each unique value. A subtotal is returned for the second item in the list (expr_2 or the combination of ( expr_2a, expr_2b, ...), whichever is specified) for each unique value in each grouping of the first item and so on. 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 ROLLUP extension specified in the context of the GROUP BY clause is shown by the following:
SELECT <select_list> FROM ... GROUP BY [... ,] ROLLUP ( <expression_list> ) [, ...]
The items specified in select_list must either:
- Also appear in the
ROLLUP expression_list - Be aggregate functions such as
COUNT,SUM,AVG,MIN,orMAX - Be constants or functions whose return values are independent of the individual rows in the group (for example, the
SYSDATEfunction)
Use the GROUP BY clause to specify multiple ROLLUP extensions as well as multiple occurrences of other GROUP BY extensions and individual expressions.
Use the ORDER BY clause to display the output in a hierarchical or other meaningful structure. Using this clause guarantees the order of the result set.
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.
Examples
The following query produces a rollup based on a hierarchy of columns loc, dname, and then 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 result of the query displays a count of the number of employees for each unique combination of loc, dname, and job, as well as subtotals for each unique combination of loc and dname for each unique value of loc. A grand total appears 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 the effect of combining items in the ROLLUP list in 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 output doesn't include subtotals for loc and dname combinations as in the prior example:
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 differ as well:
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;
Now there's a subtotal 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)