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

Syntax

A single CUBE extension uses 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 the expressions are enclosed within parentheses as (expr_1a, expr_1b, ...), the combination of values that are 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 that are returned by the expression list.
  • A subtotal is returned for each unique value of the first item in the list. This item can be expr_1 or the combination of (expr_1a, expr_1b, ...). A subtotal is returned for each unique value of the second item in the list. This item can be expr_2 or the combination of (expr_2a, expr_2b, ...). A subtotal is also returned for each unique combination of the first item and the second item. If a third item exists, a subtotal is returned for each unique value of the third item, each unique combination of the third and first items, each unique combination of the third and second items, and each unique combination of the third, second, and first items. 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.

Examples

The following example shows the CUBE extension that is specified within the GROUP BY clause:

SELECT select_list FROM ...
GROUP BY [... ,] CUBE ( expression_list ) [, ...]
  • The items specified in select_list must meet one of the following requirements: they must appear in the CUBE expression_list, 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, such as the SYSDATE function.
  • The GROUP BY clause may specify multiple CUBE extensions and multiple occurrences of other GROUP BY extensions and individual expressions.
  • If you want to display the output in a meaningful structure, you must use the ORDER BY clause. If no ORDER BY clause is specified, the order of the result set is not determined.
  • The number of grouping levels or totals is 2 raised to the power of N. 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 output shows the result of the 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, each combination of loc and job, each combination of dname and job, each unique value of loc, each unique value of dname, and each unique value of job. Then, the system generates a grand total that is 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 that involve 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)