Analytical SQL functions - rollup - cube

Ij
Posted by Ij under Oracle category on | Points: 40 | Views : 349
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,
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 CLERK 2 1900
20 ANALYST 2 6000
20 MANAGER 1 2975
20 5 10875
30 CLERK 1 950
30 MANAGER 1 2850
30 SALESMAN 4 5600
30 6 9400
14 29025

“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.
SELECT deptno,job,count(*),sum(sal) FROM emp
GROUP BY
CUBE(deptno,job);

Comments or Responses

Login to post response