Examples of Rollup and Grouping Sets

ROLLUP
-------------------------------------------------------------------------------------------
select nvl(dname,'ALL DEPARTMENTS') dname, nvl(job,'ALL JOBS') job, sum(sal) sal
from scott.emp e, scott.dept d
where d.deptno = e.deptno
group by rollup(dname, job)


GROUPING SETS
-----------------------------------------------------------------------------------------------------------
select nvl(dname,'ALL DEPARTMENTS') dname, nvl(job,'ALL JOBS') job, sum(sal) sal
from scott.emp e, scott.dept d
where d.deptno = e.deptno
group by grouping sets(dname, (dname, job))
 

No comments:

Post a Comment