CUBE extension v16

Suggest edits

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:

CUBE ( { <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 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:

SELECT <select_list> FROM ...
GROUP BY [... ,] CUBE ( <expression_list> ) [, ...]

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.

SELECT loc, dname, job, COUNT(*) AS "employees" FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY CUBE (loc, dname, job)
ORDER BY 1, 2, 3;

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.

Output
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   |            | ANALYST   |         3
BOSTON   |            | CLERK     |         3
BOSTON   |            | MANAGER   |         2
BOSTON   |            |           |         8
CHICAGO  | SALES      | CLERK     |         1
CHICAGO  | SALES      | MANAGER   |         1
CHICAGO  | SALES      | SALESMAN  |         4
CHICAGO  | SALES      |           |         6
CHICAGO  |            | CLERK     |         1
CHICAGO  |            | MANAGER   |         1
CHICAGO  |            | SALESMAN  |         4
CHICAGO  |            |           |         6
NEW YORK | ACCOUNTING | CLERK     |         1
NEW YORK | ACCOUNTING | MANAGER   |         1
NEW YORK | ACCOUNTING | PRESIDENT |         1
NEW YORK | ACCOUNTING |           |         3
NEW YORK |            | CLERK     |         1
NEW YORK |            | MANAGER   |         1
NEW YORK |            | PRESIDENT |         1
NEW YORK |            |           |         3
         | ACCOUNTING | CLERK     |         1
         | ACCOUNTING | MANAGER   |         1
         | ACCOUNTING | PRESIDENT |         1
         | ACCOUNTING |           |         3
         | OPERATIONS | ANALYST   |         1
         | OPERATIONS | CLERK     |         1
         | OPERATIONS | MANAGER   |         1
         | OPERATIONS |           |         3
         | RESEARCH   | ANALYST   |         2
         | RESEARCH   | CLERK     |         2
         | RESEARCH   | MANAGER   |         1
         | RESEARCH   |           |         5
         | SALES      | CLERK     |         1
         | SALES      | MANAGER   |         1
         | SALES      | SALESMAN  |         4
         | SALES      |           |         6
         |            | ANALYST   |         3
         |            | CLERK     |         5
         |            | MANAGER   |         4
         |            | PRESIDENT |         1
         |            | SALESMAN  |         4
         |            |           |        17
(50 rows)
The following query shows the effect of combining items in the CUBE list in parentheses:
SELECT loc, dname, job, COUNT(*) AS "employees" FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY CUBE (loc, (dname, job))
ORDER BY 1, 2, 3;
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.
Output
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
         | ACCOUNTING | CLERK     |         1
         | ACCOUNTING | MANAGER   |         1
         | ACCOUNTING | PRESIDENT |         1
         | OPERATIONS | ANALYST   |         1
         | OPERATIONS | CLERK     |         1
         | OPERATIONS | MANAGER   |         1
         | RESEARCH   | ANALYST   |         2
         | RESEARCH   | CLERK     |         2
         | RESEARCH   | MANAGER   |         1
         | SALES      | CLERK     |         1
         | SALES      | MANAGER   |         1
         | SALES      | SALESMAN  |         4
         |            |           |        17
(28 rows)
The following query shows another variation whereby the first expression is specified outside of the CUBE extension:
SELECT loc, dname, job, COUNT(*) AS "employees" FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY loc, CUBE (dname, job)
ORDER BY 1, 2, 3;
In this output, the permutations are performed for dname and job in each grouping of loc:
Output
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   |            | ANALYST   |         3
BOSTON   |            | CLERK     |         3
BOSTON   |            | MANAGER   |         2
BOSTON   |            |           |         8
CHICAGO  | SALES      | CLERK     |         1
CHICAGO  | SALES      | MANAGER   |         1
CHICAGO  | SALES      | SALESMAN  |         4
CHICAGO  | SALES      |           |         6
CHICAGO  |            | CLERK     |         1
CHICAGO  |            | MANAGER   |         1
CHICAGO  |            | SALESMAN  |         4
CHICAGO  |            |           |         6
NEW YORK | ACCOUNTING | CLERK     |         1
NEW YORK | ACCOUNTING | MANAGER   |         1
NEW YORK | ACCOUNTING | PRESIDENT |         1
NEW YORK | ACCOUNTING |           |         3
NEW YORK |            | CLERK     |         1
NEW YORK |            | MANAGER   |         1
NEW YORK |            | PRESIDENT |         1
NEW YORK |            |           |         3
(28 rows)
← Prev
ROLLUP extension
↑ Up
Multidimensional analysis
Next →
GROUPING SETS extension