Friday, October 28, 2011
ORACLE SQL TUTORIAL: ROLLUP AND CUBE
USING
ROLLUP
This
will give the salaries in each department in each job category along wih the
total salary
fot
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 8400
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
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
0 Responses to “ORACLE SQL TUTORIAL: ROLLUP AND CUBE”
Post a Comment