ROLLUP Operation: Getting sub totals
The rollup operator can be used to obtain sub- totals in a query. ROLLUP operator is an extension to the GROUP BY clause in a query that produces sub-totals in addition to the regular grouped rows.
SELECT DNAME, JOB, SUM (SAL), AVG (SAL) FROM EMP A,
(SELECT DNAME, DEPTNO FROM DEPT) B
WHERE A.DEPTNO = B.DEPTNO
GROUP BY ROLLUP (DNAME, JOB);
DNAME JOB SUM (SAL) AVG (SAL)
ACCOUNTING CLERK 1300 1300
ACCOUNTING MANAGER 3450 1725
ACCOUNTING PRESIDENT 5000 5000
ACCOUNTING 9750 2437.5
OPERATIONS MANAGER 6000 1500
OPERATIONS 6000 1500
RESEARCH ANALYST 6000 3000
RESEARCH CLERK 1900 950
RESEARCH MANAGER 2975 2975
RESEARCH 10875 2175
SALES CLERK 950 950
SALES MANAGER 2850 2850
SALES SALESMAN 5600 1400
SALES 9400 1566.66
ANALYST 6000 3000
CLERK 4150 1037.5
MANAGER 15275 1909.37
PRESIDENT 5000 5000
SALESMAN 5600 1400
36025 1896.05
CUBE Operation: Getting Cross-Tabs
The CUBE operator is an extension to the GROUP BY clause within a query that produces a result set that contains sub-totals for every possible combination of the columns in the group by clause.
SELECT DNAME, JOB, SUM (SAL), AVG (SAL) FROM EMP A,
(SELECT DNAME, DEPTNO FROM DEPT) B
WHERE A.DEPTNO = B.DEPTNO
GROUP BY CUBE (DNAME, JOB);
DNAME JOB SUM (SAL) AVG (SAL)
ACCOUNTING CLERK 1300 1300
ACCOUNTING MANAGER 3450 1725
ACCOUNTING PRESIDENT 5000 5000
ACCOUNTING 9750 2437.5
OPERATIONS MANAGER 6000 1500
OPERATIONS 6000 1500
RESEARCH ANALYST 6000 3000
RESEARCH CLERK 1900 950
RESEARCH MANAGER 2975 2975
RESEARCH 10875 2175
SALES CLERK 950 950
SALES MANAGER 2850 2850
SALES SALESMAN 5600 1400
SALES 9400 1566.66
ANALYST 6000 3000
CLERK 4150 1037.5
MANAGER 15275 1909.37
PRESIDENT 5000 5000
SALESMAN 5600 1400
36025 1896.05
