[데이터 베이스] 10-4. roll up

귤

Feb 28, 2025

Contents

roll up 사용

🔥

group by를 잘 이해하자!!

💡

job, deptno로 그룹을 짓고, job별 소계를 구하고, 전체 총계를 구한다.

😂 혼자서 roll up 쓰지 않고 해보기 - 다시 해보기


select *
from emp;

-- job, deptno 그룹화
select job, deptno, avg(sal) avg_sal, count(*) cnt
from emp
where job = 'CLERK'
group by job, deptno
union all

-- CLERK의 평균 값 구해서 그룹화
select job, null 'deptno', avg(sal) avg_sal, count(*) 'cnt'
from emp
where job = 'CLERK'
group by job
union all

-- job, deptno 그룹화
select job, deptno, avg(sal) sal, count(*) cnt
from emp
where job = 'ANALYST'
group by job, deptno
union all

-- ANALYST의 평균 값 구해서 그룹화
select job, null 'deptno', avg(sal) avg_sal, count(*) 'cnt'
from emp
where job = 'ANALYST'
group by job
union all

-- job, deptno 그룹화
select job, deptno, avg(sal) sal, count(*) cnt
from emp
where job = 'MANAGER'
group by job, deptno
union all

-- MANAGER의 평균 값 구해서 그룹화
select job, null 'deptno', avg(sal) avg_sal, count(*) 'cnt'
from emp
where job = 'MANAGER'
group by job
union all

-- job, deptno 그룹화
select job, deptno, avg(sal) sal, count(*) cnt
from emp
where job = 'SALESMAN'
group by job, deptno
union all


-- SALESMAN의 평균 값 구해서 그룹화
select job, null 'deptno', avg(sal) avg_sal, count(*) 'cnt'
from emp
where job = 'SALESMAN'
group by job
union all

-- job, deptno 그룹화
select job, deptno, avg(sal) sal, count(*) cnt
from emp
where job = 'PRESIDENT'
group by job, deptno
union all

-- PRESIDENT의 평균 값 구해서 그룹화
select job, null 'deptno', avg(sal) avg_sal, count(*) 'cnt'
from emp
where job = 'PRESIDENT'
group by job
union all

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

🔎 참고!


-- job, deptno 그룹화
select job, deptno, avg(sal) avg_sal, count(*) cnt
from emp
where job = 'CLERK'
group by job, deptno;


-- 소계
select job, null 'deptno', avg(sal) avg_sal, count(*) 'cnt'
from emp
where job = 'CLERK'
group by job; 

-- 총계
select null, null, avg(sal) avg_sal, count(*)
from emp;

roll up 사용


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

Contents

roll up 사용

select * from emp; -- job, deptno 그룹화 select job, deptno, avg(sal) avg_sal, count(*) cnt from emp where job = 'CLERK' group by job, deptno union all -- CLERK의 평균 값 구해서 그룹화 select job, null 'deptno', avg(sal) avg_sal, count(*) 'cnt' from emp where job = 'CLERK' group by job union all -- job, deptno 그룹화 select job, deptno, avg(sal) sal, count(*) cnt from emp where job = 'ANALYST' group by job, deptno union all -- ANALYST의 평균 값 구해서 그룹화 select job, null 'deptno', avg(sal) avg_sal, count(*) 'cnt' from emp where job = 'ANALYST' group by job union all -- job, deptno 그룹화 select job, deptno, avg(sal) sal, count(*) cnt from emp where job = 'MANAGER' group by job, deptno union all -- MANAGER의 평균 값 구해서 그룹화 select job, null 'deptno', avg(sal) avg_sal, count(*) 'cnt' from emp where job = 'MANAGER' group by job union all -- job, deptno 그룹화 select job, deptno, avg(sal) sal, count(*) cnt from emp where job = 'SALESMAN' group by job, deptno union all -- SALESMAN의 평균 값 구해서 그룹화 select job, null 'deptno', avg(sal) avg_sal, count(*) 'cnt' from emp where job = 'SALESMAN' group by job union all -- job, deptno 그룹화 select job, deptno, avg(sal) sal, count(*) cnt from emp where job = 'PRESIDENT' group by job, deptno union all -- PRESIDENT의 평균 값 구해서 그룹화 select job, null 'deptno', avg(sal) avg_sal, count(*) 'cnt' from emp where job = 'PRESIDENT' group by job union all select null, null, avg(sal) avg_sal, count(*) from emp;

-- job, deptno 그룹화 select job, deptno, avg(sal) avg_sal, count(*) cnt from emp where job = 'CLERK' group by job, deptno; -- 소계 select job, null 'deptno', avg(sal) avg_sal, count(*) 'cnt' from emp where job = 'CLERK' group by job; -- 총계 select null, null, avg(sal) avg_sal, count(*) from emp;