[oracle] total with pivot

DATA

직급에 따른 부서별 급여합

CUBE 사용

SELECT * FROM (
    SELECT 
        NVL(DEPTNO, -1) DEPTNO, 
        JOB, 
        SUM(SAL) SAL
    FROM EMP
    GROUP BY CUBE(DEPTNO, JOB)
) PIVOT(
    SUM(SAL) FOR DEPTNO IN (10,20,30,-1 AS TOTAL)
)
ORDER BY JOB;

SUM OVER 사용

SELECT * FROM (
    SELECT 
        DEPTNO, 
        JOB, 
        SAL, 
        SUM(SAL) OVER(PARTITION BY JOB) AS TOTAL 
    FROM EMP
) PIVOT (
    SUM(SAL) FOR DEPTNO IN (10, 20, 30)
)
ORDER BY JOB;

 

결과와 실행계획이 모두 일치한다.