![[데이터 베이스]15.rollup](https://image.inblog.dev?url=https%3A%2F%2Fsource.inblog.dev%2Ffeatured_image%2F2025-03-10T04%3A46%3A49.091Z-e000669c-9f0f-4648-aa56-449dbed6c310&w=3840&q=75)
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;
Share article