CUBE Extension v11
The CUBE
extension is similar to the ROLLUP
extension. However, unlike ROLLUP
, which produces groupings and results in a hierarchy based on a left to right listing of items in the ROLLUP
expression list, a CUBE
produces groupings and subtotals based on every permutation of all items in the CUBE
expression list. Thus, the result set contains more rows than a ROLLUP
performed on the same expression list.
The syntax for a single CUBE
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 group.
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. A subtotal is also returned for each unique combination of the first item and the second item. Similarly, if there is a third item, a subtotal is returned for each unique value of the third item, each unique value of the third item and first item combination, each unique value of the third item and second item combination, and each unique value of the third item, second item, and first item combination. 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 CUBE
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 CUBE 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 CUBE
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 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 2
raised to the power of n where n represents the number of items in the CUBE
expression list. A parenthesized list counts as one item.
The following query produces a cube based on permutations of columns loc, dname
, and job
.
The following is the result of the query. There is a count of the number of employees for each combination of loc, dname
, and job
, as well as subtotals for each combination of loc
and dname
, for each combination of loc
and job
, for each combination of dname
and job
, for each unique value of loc
, for each unique value of dname
, for each unique value of job
, and a grand total displayed on the last line.
The following query shows the effect of combining items in the CUBE
list within parenthesis.
In the output note that there are no subtotals for permutations involving loc
and dname
combinations, loc
and job
combinations, or for dname
by itself, or for job
by itself.
The following query shows another variation whereby the first expression is specified outside of the CUBE
extension.
In this output, the permutations are performed for dname
and job
within each grouping of loc
.