Mastering Oracle SQL

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 ...