Analytical SQL functions

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

1. rollup
2. cube

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.

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.

SELECT deptno, job,count(*), sum(sal) FROM emp GROUP BY ROLLUP(deptno,job);


DEPTNO JOB COUNT(*) SUM(SAL)
--------- --------- --------- ---------
10 CLERK 1 1300
10 MANAGER 1 2450
10 PRESIDENT 1 5000
10 3 8750
20 ANALYST 2 6000
20 CLERK 2 1900
20 MANAGER 1 2975
20 5 10875

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.

SELECT deptno,job,count(*),sum(sal) FROM emp GROUP BY CUBE(deptno,job);

DEPTNO JOB COUNT(*) SUM(SAL)
--------- --------- --------- ---------
10 CLERK 1 1300
10 MANAGER 1 2450
10 PRESIDENT 1 5000
10 3 8750
20 ANALYST 2 6000
20 CLERK 2 1900
20 MANAGER 1 2975
20 5 10875
30 CLERK 1 950
30 MANAGER 1 2850
30 SALESMAN 4 5600
30 6 9400
ANALYST 2 6000
CLERK 4 4150
MANAGER 3 8275
PRESIDENT 1 5000
SALESMAN 4 5600
14 29025