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)
where d.deptno = e.deptno
group by grouping sets(dname, (dname, job))
-------------------------------------------------------------------------------------------
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 dwhere d.deptno = e.deptno
group by grouping sets(dname, (dname, job))
No comments:
Post a Comment