# CUBE extensionv16

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`: