[SQL] ROLLUP 함수

27 Jun 2023 - 1 minute read

👊 소그룹 간의 소계(SUBTOTAL)와 합계(GRAND TOTAL)를 계산하는 ROLLUP 함수에 대해 알아보자 👊

▶ DEPT

▶ EMP

GROUP BY 절 사용

---

SELECT 
    B.DNAME,
    A.JOB,
    COUNT(*) AS EMP_CNT,
    SUM(A.SAL) AS SAL_SUM
FROM EMP A,
    DEPT B
WHERE B.DEPTNO = A.DEPTNO
GROUP BY B.DNAME, A.JOB;

실행결과 )

GROUP BY 절 + ORDER BY 절

---

SELECT 
    B.DNAME,
    A.JOB,
    COUNT(*) AS EMP_CNT,
    SUM(A.SAL) AS SAL_SUM
FROM EMP A,
    DEPT B
WHERE B.DEPTNO = A.DEPTNO
GROUP BY B.DNAME, A.JOB
ORDER BY B.DNAME, A.JOB;

실행결과 )

ROLLUP 함수

---

SELECT 
    B.DNAME,
    A.JOB,
    COUNT(*) AS EMP_CNT,
    SUM(A.SAL) AS SAL_SUM
FROM EMP A,
    DEPT B
WHERE B.DEPTNO = A.DEPTNO
GROUP BY ROLLUP (B.DNAME, A.JOB);

실행결과 )

GROUP BY 수행 시 생성되는 표준 집계 9건 이외에 DNAME 별 모든 JOB의 합계 3건과 모든 행의 합 1건이 추가된 것을 볼 수 있다.

ROLLUP 함수 + ORDER BY 절

---

SELECT 
    B.DNAME,
    A.JOB,
    COUNT(*) AS EMP_CNT,
    SUM(A.SAL) AS SAL_SUM
FROM EMP A,
    DEPT B
WHERE B.DEPTNO = A.DEPTNO
GROUP BY ROLLUP (B.DNAME, A.JOB)
ORDER BY B.DNAME, A.JOB;

실행결과 )

WITH ROLLUP

---

💥 MySQL에서는 WITH ROLLUP을 사용 💥

SELECT
    B.DNAME,
    A.JOB,
    COUNT(*) AS EMP_CNT,
    SUM(A.SAL) AS SAL_SUM
FROM EMP A,
    DEPT B
WHERE B.DEPTNO = A.DEPTNO
GROUP BY B.DNAME, A.JOB WITH ROLLUP;

실행결과 )