Oracle SQL에서 부서별 또는 항목별 소계(Subtotal)와 전체 합계(Total)를 함께 출력해야 할 때가 많습니다. 별도의 UNION ALL을 통해 합계 SQL만 별도의 쿼리를 사용하지 않고 구하는 방법이 있습니다. 이때 사용하는 가장 강력한 기능이 바로 ROLLUP, GROUPING, GROUPING_ID입니다. 이 글에서는 이 함수들을 이해하고 실제 데이터를 기반으로 결과까지 함께 확인해보겠습니다.
예제 데이터 (EMP 테이블 일부)
SELECT deptno, job, sal FROM emp;
| DEPTNO | JOB | SAL |
|---|---|---|
| 10 | CLERK | 1300 |
| 10 | MANAGER | 2450 |
| 20 | ANALYST | 3000 |
| 20 | ANALYST | 3000 |
| 30 | SALESMAN | 1600 |
| 30 | MANAGER | 2850 |
1. ROLLUP을 이용한 소계/합계 계산
SELECT deptno, job, SUM(sal) AS sum_sal
FROM emp
GROUP BY ROLLUP(deptno, job);
결과:
| DEPTNO | JOB | SUM_SAL |
|---|---|---|
| 10 | CLERK | 1300 |
| 10 | MANAGER | 2450 |
| 10 | NULL | 3750 |
| 20 | ANALYST | 6000 |
| 20 | NULL | 6000 |
| 30 | MANAGER | 2850 |
| 30 | SALESMAN | 1600 |
| 30 | NULL | 4450 |
| NULL | NULL | 14200 |
ROLLUP(deptno, job)을 사용하면 자동으로 소계와 합계가 함께 출력됩니다.
2. GROUPING 함수로 소계/합계 구분
GROUPING() 함수는 NULL이 실제 값인지 소계/합계인지 구분해줍니다.
SELECT
deptno,
job,
SUM(sal) AS sum_sal,
GROUPING(deptno) AS grp_dept,
GROUPING(job) AS grp_job
FROM emp
GROUP BY ROLLUP(deptno, job);
결과 요약:
| DEPTNO | JOB | SUM_SAL | GRP_DEPT | GRP_JOB |
|---|---|---|---|---|
| 10 | CLERK | 1300 | 0 | 0 |
| 10 | NULL | 3750 | 0 | 1 |
| NULL | NULL | 14200 | 1 | 1 |
GROUPING(deptno) = 1: 전체 합계GROUPING(job) = 1: JOB 항목 소계
3. LABEL 표시와 함께 사용하기
SELECT
NVL(TO_CHAR(deptno), '전체') AS 부서,
CASE
WHEN GROUPING(job) = 1 AND GROUPING(deptno) = 0 THEN '소계'
WHEN GROUPING(job) = 1 AND GROUPING(deptno) = 1 THEN '합계'
ELSE job
END AS 직무,
SUM(sal) AS 급여합계
FROM emp
GROUP BY ROLLUP(deptno, job);
결과:
| 부서 | 직무 | 급여합계 |
|---|---|---|
| 10 | CLERK | 1300 |
| 10 | MANAGER | 2450 |
| 10 | 소계 | 3750 |
| 20 | ANALYST | 6000 |
| 20 | 소계 | 6000 |
| 30 | MANAGER | 2850 |
| 30 | SALESMAN | 1600 |
| 30 | 소계 | 4450 |
| 전체 | 합계 | 14200 |
CASE와 GROUPING()을 함께 사용하면 비즈니스 보고서에서 요구하는 라벨링된 소계/합계도 쉽게 구현할 수 있습니다.
마무리
Oracle의 ROLLUP, GROUPING, GROUPING_ID는 반복적인 소계/합계 계산을 매우 간단하고 효율적으로 만들어줍니다. 특히 UNION ALL로 쿼리를 일일이 만들어 합계를 붙이던 방식보다 훨씬 효율적이고 관리도 쉬워집니다. 보고서나 대시보드 화면의 집계 쿼리를 개선하고 싶다면 꼭 활용해보시기 바랍니다.