Analytical Function

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

 

 

Advertisements