WITH DEPT
AS (SELECT 'A' AS P_DEPT, 'AAAA' AS NAME, 12.15 AS AMT FROM DUAL
UNION ALL
SELECT 'A' AS P_DEPT, 'BBBB' AS NAME, 22.15 AS AMT FROM DUAL
UNION ALL
SELECT 'A' AS P_DEPT, 'AAAA' AS NAME, 25.15 AS AMT FROM DUAL
UNION ALL
SELECT 'B' AS P_DEPT, 'DDDD' AS NAME, 9.15 AS AMT FROM DUAL
UNION ALL
SELECT 'B' AS P_DEPT, 'BBBB' AS NAME, 45.15 AS AMT FROM DUAL)
SELECT GROUPING(P_DEPT) AS D
, GROUPING(NAME) AS N
, GROUPING_ID(P_DEPT, NAME) AS TOTAL
, GROUP_ID() AS GRP
, P_DEPT
, NAME
, SUM(AMT) AS SUM_AMT -- 실제금액
, ROUND(SUM(AMT), 1) AS RND_SUM_CNT -- 항목의 합과 합계가 일치 하지 않는다. BUT 합계의 값은 실제 전체 금액을 마지막에 ROUND하기에 실제 값과 갭이 적다.
, SUM(ROUND(AMT, 1)) AS SUM_RND_CNT -- 항목의 값을 ROUND한 다음 SUM하기 때문에 항목과 합계가 일치하지만 실제 값과 갭이 커진다.
FROM DEPT
GROUP BY GROUPING SETS ((P_DEPT, NAME), (P_DEPT), (), (P_DEPT, P_DEPT));
D N TOTAL GRP P NAME SUM_AMT RND_SUM_CNT SUM_RND_CNT
---------- ---------- ---------- ---------- - ---- ---------- --------------------------------------- ---------------------------------------
0 0 0 0 A AAAA 37.3 37.3 37.4
0 0 0 0 A BBBB 22.15 22.2 22.2
0 0 0 0 B BBBB 45.15 45.2 45.2
0 0 0 0 B DDDD 9.15 9.2 9.2
1 1 3 0 113.75 113.8 114
0 1 1 0 A 59.45 59.5 59.6
0 1 1 0 B 54.3 54.3 54.4
0 1 1 1 A 59.45 59.5 59.6
0 1 1 1 B 54.3 54.3 54.4
9개의 행이 선택됨 GROUPING(P_DEPT) -> P_DEPT 가 NULL이면 1, 아니면 0
GROUPING_ID(P_DEPT, NAME) -> GROUPING(P_DEPT) || GROUPING(NAME) 를 2진수로 보고 10진수로 환산
GROUP_ID() -> 동일항목 롤업시(P_DEPT, P_DEPT) 구분
* 아래 두 집계의 결과는 같다
SELECT GROUPING_ID(P_DEPT, NAME)
, P_DEPT
, NAME
, SUM(AMT)
FROM DEPT
GROUP BY ROLLUP (P_DEPT, NAME);
SELECT GROUPING_ID(P_DEPT, NAME)
, P_DEPT
, NAME
, SUM(AMT)
FROM DEPT
GROUP BY GROUPING SETS ((P_DEPT, NAME), (P_DEPT), ());
GROUPING_ID(P_DEPT,NAME) P_DEPT NAME SUM(AMT)
--------------------------------------- ------ ---- ----------
0 A AAAA 37.3
0 A BBBB 22.15
1 A 59.45
0 B BBBB 45.15
0 B DDDD 9.15
1 B 54.3
3 113.75
* CUBE
SELECT GROUPING_ID(P_DEPT, NAME)
, P_DEPT
, NAME
, SUM(AMT)
FROM DEPT
GROUP BY CUBE (P_DEPT, NAME)
ORDER BY P_DEPT, NAME;
GROUPING_ID(P_DEPT,NAME) P_DEPT NAME SUM(AMT)
--------------------------------------- ------ ---- ----------
0 A AAAA 37.3
0 A BBBB 22.15
1 A 59.45
0 B BBBB 45.15
0 B DDDD 9.15
1 B 54.3
2 AAAA 37.3
2 BBBB 67.3
2 DDDD 9.15
3 113.75