These are the enhancements to the group by feature.
USING ROLLUP
This will give the salaries in each department in each job
category along wih the total salary for individual departments and the total
salary of all the departments.
SQL> Select
deptno,job,sum(sal) from emp group by rollup(deptno,job);
DEPTNO
JOB SUM(SAL)
----------
--------- ----------
10 CLERK 1300
10 MANAGER 2450
10
PRESIDENT 5000
10 8750
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 10875
30 CLERK 950
30 MANAGER
2850
30 SALESMAN 5600
30 9400
29025
USING GROUPING
In the above query it will
give the total salary of the individual departments but with a
blank in the job column and
gives the total salary of all the departments with blanks in
deptno and job columns.
To replace these blanks
with your desired string grouping will be used
SQL> select
decode(grouping(deptno),1,'All Depts',deptno),decode(grouping(job),1,'All
jobs',job),sum(sal) from emp group by rollup(deptno,job);
DECODE(GROUPING(DEPTNO),1,'ALLDEPTS',DEP
DECODE(GR SUM(SAL)
----------------------------------- ---------------------------------- --------------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 All jobs 8750
20
ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 All jobs 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 All jobs 9400
All Depts All jobs 29025
Grouping will return 1 if
the column which is specified in the grouping function has been
used in rollup.
Grouping will be used in
association with decode.
USING CUBE
This will give the salaries in each department in each job
category, the total salary for individual departments, the total salary of all
the departments and the salaries in each job category.
SQL> select
decode(grouping(deptno),1,’All Depts’,deptno),decode(grouping(job),1,’All
Jobs’,job),sum(sal)
from emp group by cube(deptno,job);
DECODE(GROUPING(DEPTNO),1,'ALLDEPTS',DEP
DECODE(GR SUM(SAL)
----------------------------------- ------------------------------------ ------------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 All Jobs 8750
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 All Jobs 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 All Jobs 9400
All Depts ANALYST 6000
All Depts CLERK 4150
All Depts MANAGER 8275
All Depts PRESIDENT 5000
All Depts SALESMAN 5600
All Depts All Jobs 29025
No comments:
Post a Comment