ROLLUP Extension v12
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 leftmost item in the list. Each successive item proceeding to the right moves down the hierarchy with the rightmost item being the lowest level.
The syntax for a single ROLLUP
is as follows:
Each expr
is an expression that determines the grouping of the result set. If enclosed within 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, within 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 within the context of the GROUP BY
clause is shown by the following:
The items specified in select_list
must also appear in the ROLLUP expression_list
; or 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 (for example, the SYSDATE
function).
The GROUP BY
clause may specify multiple ROLLUP
extensions as well as multiple occurrences of other GROUP BY
extensions and individual expressions.
The ORDER BY
clause should be used if you want the output to display in a hierarchical or other meaningful structure. There is no guarantee on the order of the result set 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 produces a rollup based on a hierarchy of columns loc, dname
, then job
.
The following is the result of the query. There is 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
, and a grand total displayed on the last line.
The following query shows the effect of combining items in the ROLLUP
list within parenthesis.
In the output, note that there are no subtotals for loc
and dname
combinations as in the prior example.
If the first two columns in the ROLLUP
list are enclosed in parenthesis, the subtotal levels differ as well.
Now there is a subtotal for each unique loc
and dname
combination, but none for unique values of loc
.