12.4 GROUPING SETS

Earlier in this chapter, you saw how to generate summary information using ROLLUP and CUBE. However, the output of ROLLUP and CUBE include the rows produced by the regular GROUP BY operation along with the summary rows. Oracle9i introduces another extension to the GROUP BY clause called GROUPING SETS that you can use to generate summary information at the level you choose without including all the rows produced by the regular GROUP BY operation.

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

SELECT ...
FROM ...
GROUP BY GROUPING SETS (list of grouping columns)

Let's take an example to understand the GROUPING SETS operation further.

SELECT O.YEAR, TO_CHAR(TO_DATE(O.MONTH, 'MM'), 'Month') MONTH,
R.NAME REGION, SUM(O.TOT_SALES)
FROM ORDERS O, REGION R
WHERE R.REGION_ID = O.REGION_ID
AND O.MONTH BETWEEN 1 AND 3
GROUP BY GROUPING SETS (O.YEAR, O.MONTH, R.NAME);

      YEAR MONTH     REGION               SUM(O.TOT_SALES)
---------- --------- -------------------- ----------------
      2000                                        10042570
      2001                                         5021285
           January                                 4496799
           February                                4988535
           March                                   5578521
                     Mid-Atlantic                  5029212
                     New England                   5074332
                     SouthEast US                  4960311

8 rows selected.

Note that the output contains only the subtotals at the region, month, and year levels, but that none of the normal, more detailed, GROUP BY data is included. The order of columns in the GROUPING SETS operation is not critical. The operation produces the same output regardless of the order of the columns, except that the sequence of the rows in the output will be as per the sequence of the columns in the GROUPING operation. For example, if you alter the order of the columns from (O.YEAR, O.MONTH, R.NAME) to (O.MONTH, R.NAME, O.YEAR), the summary rows for the month will be displayed first, followed by the summary rows for the region, and then the summary rows for the year. For example:

SELECT O.YEAR, TO_CHAR(TO_DATE(O.MONTH, 'MM'), 'Month') MONTH,
R.NAME REGION, SUM(O.TOT_SALES)
FROM ORDERS O, REGION R
WHERE R.REGION_ID = O.REGION_ID
AND O.MONTH BETWEEN 1 AND 3
GROUP BY GROUPING SETS (O.MONTH, R.NAME, O.YEAR);

      YEAR MONTH     REGION               SUM(O.TOT_SALES)
---------- --------- -------------------- ----------------
           January                                 4496799
           February                                4988535
           March                                   5578521
                     Mid-Atlantic                  5029212
                     New England                   5074332
                     SouthEast US                  4960311
      2000                                        10042570
      2001                                         5021285

8 rows selected.