Mastering Oracle SQL

Chapter 12. Advanced Group Operations

Group operations aggregate data over multiple rows. We discussed the GROUP BY clause and basic group operations in Chapter 4. Decision support systems require more complex group operations. Data warehousing applications involve aggregation over multiple dimensions of data. To enable effective decision support, you need to summarize transaction data at various levels. We discuss advanced group operations used by decision support systems in this chapter.

Oracle8i introduced several handy extensions to SQL’s ability to summarize data. These include the following:

• A ROLLUP function to insert totals and subtotals into summarized results.

• A CUBE function to generate subtotals for all possible combinations of grouped columns.

• A GROUPING function to help correctly interpret results generated using CUBE and ROLLUP.

In Oracle9i, yet another function was introduced to generate summary information at a specific level: the GROUPING SETS function.

ROLLUP

In Chapter 4, you saw how the GROUP BY clause, along with the aggregate functions, can be used to produce summary results. For example, if you want to print the monthly total sales for each region, you would probably execute the following query:

            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 AND YEAR = 2001
            GROUP BY R.NAME, O.MONTH; REGION MONTH SUM(O.TOT_SALES) -------------------- --------- ---------------- ...