A CUBE extension is similar to the ROLLUP extension. However, a ROLLUP extension generates groupings and results in a hierarchy based on a left-to-right listing of items in the ROLLUP expression list. The CUBE extension generates groupings and subtotals based on every permutation of all items in the CUBE expression list. The result set contains more rows than a ROLLUP extension used in the same expression list.

A single CUBE expression has the following syntax:

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 within parentheses as ( expr_1a, expr_1b, ...), 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 corresponds to each unique combination of values returned by the expression list.

A subtotal of each unique value is returned by the first item in the list. This item can be expr_1 or the combination of ( expr_1a, expr_1b, ...). A subtotal of each unique value is returned by the second item in the list. This item can be expr_2 or the combination of ( expr_2a, expr_2b, ...). A subtotal of each unique combination is also returned by the first item and the second item. Similarly, if a third item exists, a subtotal of each unique value is returned by the third item, a subtotal of each unique combination is returned by the third item and first item, a subtotal of each unique combination is returned by the third item and second item, and a subtotal of each unique combination is returned by the third item, second item, and first item. 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 following example shows the CUBE extension specified within the context of the GROUP BY clause:

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

The items specified in select_list must appear in the CUBE expression_list, be aggregate functions such as COUNT, SUM, AVG, MIN, or MAX, or be constants or functions such as the SYSDATE function whose returned values are independent of the individual rows in the group.

The GROUP BY clause may specify multiple CUBE extensions and multiple occurrences of other GROUP BY extensions and individual expressions.

You must use the ORDER BY clause if you want to display the output in a meaningful structure. The order of the result set is not determined 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 generates a cube based on permutations of the loc, dname, and job columns.

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 example shows the result of this query. The system calculates the number of employees for each combination of loc, dname, and job. The system also calculates the 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, and for each unique value of job. Then, the system generates a grand total displayed on the last line.

   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 how to combine the items in the CUBE list within 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 following output shows that no subtotals are generated for permutations involving the combinations of loc and dname and the combinations of loc and job, or for dname or job.

   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 the following output, the permutations are performed for dname and job within each grouping of loc.

   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)