Use the ROLLUP OPERATOR to produce subtotal values, ROLLUP IS AN EXTENSION OF GROUP BY CLAUSE, Use the CUBE OPERATOR to produce cross-tabulation values. Use the GROUPING function to identify the row values created by ROLLUP or CUBE. These operators and GROUPING function can best be used ALONG WITH GROUP FUNCTIONS, as group functions operate on a set of rows to give one result per group.
Examples of Rollup:
SQL> select deptno,sum(sal) from emp where deptno <30 group by
rollup(deptno);
DEPTNO SUM(SAL)
---------- ----------
10 8750
20 10875
19625
SQL> select
deptno,job,sum(sal) from emp where deptno <30 group by
rollup(deptno,job);
DEPTNO JOB SUM(SAL)
---------- --------- ----------
10
CLERK 1300
10
MANAGER 2450
10
PRESIDENT 5000
10 8750
20
CLERK 1900
20
ANALYST 6000
20
MANAGER 2975
20 10875
19625
Example of CUBE:
The cube operator is used to produce results sets that are
typically used for cross-tabular reports. This means Rollup produces only one
possible subtotaling where as Cube produces subtotal for all possible
conditions of grouping specified in the group by clause and a grand total
SQL> select
deptno,sum(sal) from emp where deptno <30 group by cube(deptno);
DEPTNO SUM(SAL)
---------- ----------
19625
10 8750
20 10875
The
following query produces subtotaling results based on job,based on deptno and
based on the individual jobs(clerk or analyst or manager etc in dept 10 and 20)
SQL> select
deptno,job,sum(sal) from emp where deptno <30
group by cube(deptno,job);
DEPTNO
JOB SUM(SAL)
---------- --------- ----------
19625
CLERK 3200
ANALYST 6000
MANAGER 5425
PRESIDENT 5000
10 8750
10
CLERK 1300
10
MANAGER 2450
10
PRESIDENT 5000
20 10875
20
CLERK 1900
DEPTNO
JOB SUM(SAL)
---------- --------- ----------
20
ANALYST 6000
20
MANAGER 2975
13 rows
selected.
No comments:
Post a Comment