[데이터 베이스] 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;

See more posts

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

roll up 사용

More articles

[데이터 베이스] 11. DBMS 동작 원리

[데이터 베이스] 10-5. pivot

[데이터 베이스] 10-3. 파티션 별 순위 구하기

[데이터 베이스] 10-2. 순위 구하기 rownum 변수 활용