Mastering Oracle SQL

CUBE

The CUBE extension of the GROUP BY clause takes aggregation one step further than ROLLUP. The CUBE operation generates subtotals for all possible combinations of the grouping columns. Therefore, output of a CUBE operation will contain all subtotals produced by an equivalent ROLLUP operation and also some additional subtotals. For example, if you are performing ROLLUP on columns region and month, you will get subtotals for all months for each region, and a grand total. However, if you perform the corresponding CUBE, you will get:

• The regular rows produced by the GROUP BY clause

• Subtotals for all months on each region

• A subtotal for all regions on each month

• A grand total

Like ROLLUP, CUBE is an extension of the GROUP BY clause, and can appear in a query only along with a GROUP BY clause. The syntax of CUBE is:

SELECT ...
FROM ...
GROUP BY CUBE (list of grouping columns)

For example, the following query returns subtotals for all combinations of regions and months in the ORDER table:

            SELECT R.NAME REGION, TO_CHAR(TO_DATE(O.MONTH, 'MM'), 'Month') MONTH, 
            SUM(O.TOT_SALES)
            FROM ORDERS O, REGION R
            WHERE R.REGION_ID = O.REGION_ID
            GROUP BY CUBE(R.NAME, O.MONTH); REGION MONTH SUM(O.TOT_SALES) -------------------- --------- ---------------- Mid-Atlantic January 1832091 Mid-Atlantic February 1286028 Mid-Atlantic March 1911093 Mid-Atlantic April 1623438 Mid-Atlantic May 1778805 Mid-Atlantic June 1504455 Mid-Atlantic July 1820742 Mid-Atlantic August 1381560 Mid-Atlantic September 1178694 ...