[데이터 베이스]15.rollup

손영민

Mar 09, 2025

group by를 잘 이해하자.

ROLLUP은 그룹별 소계를 자동으로 계산하는 기능입니다.

즉, GROUP BY와 함께 사용하면 부분 합계(소계)와 전체 합계를 자동으로 추가할 수 있습니다.

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


-- 6. rollup (집계 함수)
-- 같은 job에서 같은 부서 끼리
select job, deptno, avg(sal) sal, count(*) cnt
from emp
where job = 'clerk'
group by job, deptno;


select 'cleak', null , 1017 , 3; 

select job
from emp
where job = 'clerk'
group by job;


select job, null
from emp
where job = 'clerk'
group by job;


select null;

--
select '1017';

select avg(sal)
from
(
select avg(sal) sal
from emp
where job = 'clerk'
group by job, deptno
) e;


-- 


select job, null, avg(sal), count(*) cnt
from emp
where job = 'clerk'
group by job;

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

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

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

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

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

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

-- 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;

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

See more posts

[데이터 베이스]15.rollup

More articles

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

[데이터 베이스]16.piot

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

[데이터 베이스]13.순위구하기(rownum변수 활용)