Better Oracle Analytics with Advanced SQL Aggregations

Learn Oracle Analytics with (advanced) SQL aggregations such as LISTAGG, ROLLUP and CUBE!

Our Oracle Advanced SQL Tutorial Series provides an in-depth look at Oracle’s Advanced SQL capabilities. Previous tutorials have reviewed features such as Analytic Functions, Advanced Use of Oracle DECODE CASE PIVOT and UNPIVOT, Advanced Use of Subqueries, Adaptive Cursor Sharing, Record Locking in APEX and other Connection Pooled Environments, and a host of other topics.

This session addresses Advanced Reporting of Aggregated Data Using Group Functions. It will cover the following major topics:
• LISTAGG function to concatenate values of a column from multiple rows into a single output column in one row
• ROLLUP extension to GROUP BY to automatically calculate subtotals and grand totals for a sorted report
• CUBE extension to GROUP BY to automatically calculate subtotals and grand totals for all possible combinations of sorted data
• Grouping Functions to enable proper labelling of subtotals
• Grouping Functions with DECODE or CASE to label subtotals appropriately
• Grouping Sets extension to GROUP BY to specify exactly which groups are to be subtotaled
• Use FIRST and LAST to find the lowest and highest subtotal within a group total (e.g. display the average sales by region, the average sales with the best credit level for that region, and the average sales with the worst credit level for that region)

Prerequisite: Basic knowledge of SQL GROUP BY

Presenters
Geoff Wiland
Tyson Jouglet
David Anderson