![[Database] 18. ROLL UP](https://image.inblog.dev?url=https%3A%2F%2Fsource.inblog.dev%2Ffeatured_image%2F2025-02-28T07%3A16%3A13.598Z-b28fb765-1751-41d9-98de-cb088e88641a&w=3840&q=75)
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;
Share article