GROUP BY ROLLUP, 오라클 합계 소계

오라클 합계, 소계 구하기

GROUP BY ROLLUP을 이용하면 합계와 소계를 쉽게 구할 수 있다.

WITH TMP AS (

SELECT 'AA' AS DEPTNO, '111' AS EMPNO, 100 AS SAL FROM DUAL UNION ALL

SELECT 'AA' AS DEPTNO, '111' AS EMPNO, 200 AS SAL FROM DUAL UNION ALL

SELECT 'AA' AS DEPTNO, '222' AS EMPNO, 110 AS SAL FROM DUAL UNION ALL

SELECT 'BB' AS DEPTNO, '333' AS EMPNO, 120 AS SAL FROM DUAL UNION ALL

SELECT 'BB' AS DEPTNO, '444' AS EMPNO, 130 AS SAL FROM DUAL UNION ALL

SELECT 'CC' AS DEPTNO, '555' AS EMPNO, 140 AS SAL FROM DUAL UNION ALL

SELECT 'CC' AS DEPTNO, '666' AS EMPNO, 150 AS SAL FROM DUAL

)

SELECT

          DEPTNO

        , CASE GROUPING_ID(DEPTNO, EMPNO) WHEN 1 THEN '소계'

                                                             WHEN 3 THEN '합계'

                                                             ELSE EMPNO

           END AS EMPNO

        , SUM(SAL) AS SAL

        , GROUPING(DEPTNO) AS DEPTNO_G

        , GROUPING(EMPNO) AS EMPNO_G

        , GROUPING_ID(DEPTNO, EMPNO) AS GP_ID

        --, GROUP_ID() AS G_ID

 FROM TMP

GROUP BY ROLLUP(DEPTNO, EMPNO)

--HAVING GROUPING_ID(DEPTNO, EMPNO) != 3 -- 소계만

--HAVING GROUPING_ID(DEPTNO, EMPNO) != 1 -- 합계만

--GROUP BY CUBE(DEPTNO, EMPNO)

--GROUP BY ROLLUP((DEPTNO, EMPNO)) -- 괄호로 묶으면 하나의 항목처럼 처리가능

=> 결과

DEPTNO  EMPNO  SAL  DEPTNO_G  EMPNO_G  GP_ID

----------------------------------------------------------------------------------------

AA         111        300              0              0        0

AA         222        110              0              0        0

AA         소계       410              0              1        1

BB         333        120              0              0        0

BB         444        130              0              0        0

BB         소계       250              0              1        1

CC         555        140              0              0        0

CC         666        150              0              0        0

CC         소계       290              0              1        1

             합계       950              1              1        3

 

GROUP BY ROLLUP

그룹별로 합을 추가

GROUP BY CUBE

모든 경우의 수에 대해 그룹핑

GROUPING(), GROUPING_ID(), GROUP_ID()

ROLLUP, CUBE 사용시 발생된 결과를 구분하기 위한 함수

GROUPING(A)

A컬럼으로 그룹이 지어진결과는 0, 합산된 결과는 1

GROUPING_ID(A, B)

GROUPING(A) || GROUPING(B)를 2진수로 보고 10진수로 환산

00 -> 0, 01 -> 1, 11 -> 3

GROUP_ID()

GROUP BY ROLLUP(A, A)

동일 항목으로 롤업했을때 결과를 구별하기 위해 사용