[데이터베이스] 81. 통계쿼리 함수들 (4) roll up

서회정

Feb 28, 2025

Contents

정리

📌

group by를 잘 이해하고 활용해보자.

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

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


-- 6. rollup (집계 함수)
select job, deptno, avg(sal) sal, count(*) cnt
from emp
where job='clerk'
group by job, deptno
order by deptno asc;

-- 순서
select *
from emp;

select *
from emp
where job = 'clerk';

--------------------------------------------------
-- 노가다코드

select job, deptno, avg(sal) avg_sal, count(*) 'cnt'
from emp
where job = 'CLERK'
group by job, deptno
union all
select null, null, avg(sal) avg_sal, count(*) 'cnt'
from emp
where job = 'CLERK'
group by job
union all
select job, deptno, avg(sal) sal, count(*) cnt
from emp
where job = 'ANALYST'
group by job, deptno
union all
select null, null, avg(sal) avg_sal, count(*) 'cnt'
from emp
where job = 'ANALYST'
group by job
union all
select job, deptno, avg(sal) sal, count(*) cnt
from emp
where job = 'MANAGER'
group by job, deptno
union all
select null, null, avg(sal) avg_sal, count(*) 'cnt'
from emp
where job = 'MANAGER'
group by job
union all
select job, deptno, avg(sal) sal, count(*) cnt
from emp
where job = 'SALESMAN'
group by job, deptno
union all
select null, null, avg(sal) avg_sal, count(*) 'cnt'
from emp
where job = 'SALESMAN'
group by job
union all
select job, deptno, avg(sal) sal, count(*) cnt
from emp
where job = 'PRESIDENT'
group by job, deptno
union all
select null, null, avg(sal) avg_sal, count(*) 'cnt'
from emp
where job = 'PRESIDENT'
group by job;
-------------------------------------------------
-- 안노가다 코드

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;