[Database] 18. ROLL UP

1. 노가다 코드

select job, deptno, avg(sal), count(*) cnt
from emp
where job = 'CLERK'
group by job, deptno

union all

select job, null deptno, avg(sal), count(*)
from emp
where job = 'CLERK'

union all

select job, deptno, avg(sal), count(*) cnt
from emp
where job = 'SALESMAN'
group by job, deptno

union all

select job, null deptno, avg(sal), count(*)
from emp
where job = 'SALESMAN'

union all

select job, deptno, avg(sal), count(*) cnt
from emp
where job = 'ANALYST'
group by job, deptno

union all

select job, null deptno, avg(sal), count(*)
from emp
where job = 'ANALYST'

union all

select job, deptno, avg(sal), count(*) cnt
from emp
where job = 'MANAGER'
group by job, deptno

union all

select job, null deptno, avg(sal), count(*)
from emp
where job = 'MANAGER'

union all

select job, deptno, avg(sal), count(*) cnt
from emp
where job = 'PRESIDENT'
group by job, deptno

union all

select job, null deptno, avg(sal), count(*)
from emp
where job = 'PRESIDENT'

union all

select null, null, avg(sal), count(*)
from emp;

2. Rollup 사용 코드

select job, deptno, avg(sal), count(*) cnt
from emp
group by job, deptno with rollup;