">//ROLLUP = UNION사용
select list1, list2, sum(sal)
from emp
group by rollup(list1, list2)
----------------------------------------
//Union사용
select list1, list2, sum(sal)
from emp
group by rollup(list1, list2)
UNION
select list1, list2, sum(sal)
from emp
group by rollup(list1, null)
UNION
select list1, list2, sum(sal)
from emp
group by rollup(null, null)
CUBE-집계함수
//CUBE 함수
select list1, list2, sum(sal)
from emp
group by cube(list1, list2)
------------------------------------------
//UNION사용
select list1, list2, sum(sal)
from emp
group by rollup(list1, list2)
UNION
select list1, list2, sum(sal)
from emp
group by rollup(list1, null)
UNION
select list1, list2, sum(sal)
from emp
group by rollup(null, list2) //이 부분이 ROLLUP과 차이점
UNION
select list1, list2, sum(sal)
from emp
group by rollup(null, null)
여러가지 형태 rollup, cube, grouping
Q1
select deptno, to_char(hiredate,'yyyy') as hd,job, sum(sal)
from emp
group by rollup((to_char(hiredate,'yyyy'),job),deptno);
Q2
select deptno, to_char(hiredate,'yyyy') as hd, sum(sal)
from emp
group by rollup(deptno, to_char(hiredate,'yyyy'));
Q3
select deptno, to_char(hiredate,'yyyy'), job, sum(sal)
from emp
group by job, rollup ((to_char(hiredate,'yyyy'),deptno));
Q4
select deptno, to_char(hiredate,'yyyy'), job, sum(sal)
from emp
group by job,to_char(hiredate,'yyyy'), rollup (deptno);
Q5
select deptno, to_char(hiredate,'yyyy'), sum(sal)
from emp
group by cube(deptno, to_char(hiredate,'yyyy'));
Q6
select deptno, to_char(hiredate,'yyyy'), job, sum(sal)
from emp
group by job, rollup((deptno, to_char(hiredate,'yyyy')));
Q7
select deptno, to_char(hiredate,'yyyy'),sum(sal)
from emp
group by deptno,to_char(hiredate,'yyyy');
Q8
select deptno,to_char(hiredate,'yyyy'), job, sum(sal)
from emp
group by cube(deptno, (to_char(hiredate,'yyyy'),job));
Q9
select deptno, empno,
decode(grouping_id(1,deptno),1,'SUM',3,'AVG',ename) as ename
,decode(grouping_id(1,deptno),3, round(avg(sal), 2),sum(sal)) as SUM_SAL
,grouping(1) as "1"
,grouping(deptno) as "D"
--,grouping(empno) as "E"
,grouping_id(1,deptno) as "ID"
from emp
where deptno = 30
group by rollup (1,(empno, ename, deptno));
Q10
select deptno
,empno
,decode(grouping_id(1,deptno,2,empno),1,'DEPT_SUM', 3, 'DEPT_AVG',7, 'TOTAL_SUM', 15, 'TOTAL_AVG', ename) as ename
,decode(grouping_id(1,deptno,2,empno), 3,round(avg(sal),1),15,round(avg(sal),1),sum(sal)) as sal
,grouping(1) as "1"
,grouping(deptno) as "D"
,grouping(2) as "2"
,grouping(empno) as "E"
,grouping_id(1,deptno,empno,2) as "ID"
from emp
group by rollup(1,deptno,2,(ename,empno))
order by deptno,empno,ename desc;
--------------------------------Q2 day5
