Analytical SQL functions - rollup - cube

 

Oracle has introduced some exciting extensions to ANSI SQL to allow us to quickly compute aggregations and rollups.  These new statements include:

 

• rollup
• cube
• grouping

 

 

These simple SQL operators allow us to create easy aggregations directly inside the SQL without having to employ SQL*Plus break and compute statements. Let’s start by examining the ROLLUP syntax.

 

 

·        Creating tabular aggregates with ROLLUP

 

ROLLUP enables an SQL statement to calculate multiple levels of subtotals across a specified group of dimensions. It also calculates a grand total. ROLLUP is a simple extension to the GROUP BY clause, so its syntax is extremely easy to use.

 

 

 

 

 

·        Create cross-tabular reports with CUBE

 

In multidimensional jargon, a “cube” is a cross-tabulated summary of detail rows.  CUBE enables a SELECT statement to calculate subtotals for all possible combinations of a group of dimensions. It also calculates a grand total. This is the set of information typically needed for all cross-tabular reports, so CUBE can calculate a cross-tabular report with a single select statement. 

 

Note in the example below that totals are calculated for each department, and also for each job category.