1. Home
  2. SQL
  3. Mastering Oracle SQL

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.



    		Mastering Oracle SQL, 2nd Edition
    		Preface
    		Chapter 1. Introduction to SQL
    		Chapter 2. The WHERE Clause
    		Chapter 3. Joins
    		Chapter 4. Group Operations
    		Chapter 5. Subqueries
    		Chapter 6. Handling Temporal Data
    		Chapter 7. Set Operations
    		Chapter 8. Hierarchical Queries
    		Chapter 9. DECODE and CASE
    		Chapter 10. Partitioning
    		Chapter 11. PL/SQL
    		Chapter 12. Objects and Collections
    		Chapter 13. Advanced Group Operations
    		13.1 Multiple Summary Levels
    		13.2 Pushing the GROUPING Envelope
    		13.3 The GROUPING_ID and GROUP_ID Functions
    		Chapter 14. Advanced Analytic SQL
    		Chapter 15. SQL Best Practices
    		Chapter 16. XML
    		Chapter 17. Regular Expressions
    		Chapter 18. Model Queries
    		Appendix A. Oracle's Old Join Syntax
    		Colophon