O'Reilly logo

Mastering Oracle SQL, 2nd Edition by Alan Beaulieu, Sanjay Mishra

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

Oracle provides several handy SQL features to summarize data. These include the following:

  • A ROLLUP function to generate totals and subtotals in the summarized results.

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

  • A GROUPING SETS function to generate summary information at the level you choose without including all the rows produced by the regular GROUP BY operation.

  • The GROUPING, GROUPING_ID and GROUP_ID functions to help you correctly interpret results generated using ROLLUP, CUBE, and GROUPING SETS.

Multiple Summary Levels

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 all_orders o JOIN region r
            ON r.region_id = o.region_id
            GROUP BY r.name, o.month; REGION MONTH SUM(O.TOT_SALES) ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required