# CUBE extension v16

The `CUBE`

extension is similar to the `ROLLUP`

extension. However, `ROLLUP`

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.

## Syntax

The syntax for a single `CUBE`

is:

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 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's 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
- 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 in the context of the `GROUP BY`

clause is shown by the following:

The items specified in `select_list`

must either:

- Also appear in the
`CUBE expression_list`

- Be aggregate functions such as
`COUNT`

,`SUM`

,`AVG`

,`MIN`

, or`MAX`

- Be constants or functions whose return values are independent of the individual rows in the group (for example, the
`SYSDATE`

function)

Use the `GROUP BY`

clause to specify multiple `CUBE`

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 meaningful structure and to guarantee the order of the result set.

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.

## Examples

The following query produces a cube based on permutations of columns `loc`

, `dname`

, and `job`

.

The following is the result of the query. It displays 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`

- Each combination of
`loc`

and`job`

- Each combination of
`dname`

and`job`

- Each unique value of
`loc`

- Each unique value of
`dname`

- Each unique value of
`job`

A grand total appears on the last line.

The following query shows the effect of combining items in the `CUBE`

list in parentheses:

The output has no subtotals for permutations involving `loc`

and `dname`

combinations, `loc`

and `job`

combinations, 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`

in each grouping of `loc`

: