[oracle]oracle sql 에서 총계, 소계 구하는 방법

금액과 관련된 웹 화면에서 총계, 소계를 구해서 보여줘야 할 때가 많다.
아래와 같은 방식으로 보여주면 된다. --1)일반 방식 --JOB_ID, DEPARTMENT_ID, MANAGER_ID 가 모두 같을 때의 SALARY SUM SELECT A.JOB_ID, A.DEPARTMENT_ID, A.MANAGER_ID, SUM(A.SALARY) FROM EMPLOYEES A GROUP BY A.JOB_ID, A.DEPARTMENT_ID, A.MANAGER_ID; --2)총계, 소계 구하기 --위의 일반 방식에 추가로, JOB_ID, DEPARTMENT_ID 가 같을 때의 SALARY SUM (소계), JOB_ID 가 같을 때의 SALARY SUM (소계), 항목에 상관없이 전체 SUM을 구하고 싶음(총계) --GROUP BY ROLLUP() 사용 */ SELECT A.JOB_ID, A.DEPARTMENT_ID, A.MANAGER_ID, SUM(A.SALARY) FROM EMPLOYEES A GROUP BY ROLLUP(A.JOB_ID, A.DEPARTMENT_ID, A.MANAGER_ID); --3)총계, 소계 순서 조정 --총계는 맨위로, 소계는 소계가 되는 항목들 맨 위로 올리기 --ROLLUP을 사용하면, GROUPING_ID(JOB_ID, DEPARTMENT_ID, MANAGER_ID) 를 조회할 수 있다. --JOB_ID, DEPARTMENT_ID, MANAGER_ID 가 같은 ROW 는 0 --JOB_ID, DEPARTMENT_ID 가 같은 ROW 는 1 (=0*2+1) --JOB_ID 가 같은 ROW는 3 (=1*2+1) --모두 같은 ROW 는 7 (=3*2+1) --그러나 ORDER BY GROUPING_ID(JOB_ID, DEPARTMENT_ID, MANAGER_ID) DESC 를 하면, 총계와 소계에 관한 ROW들만 맨 위로 올라와서, 처음에 목적에 부합하지 않는다. --총계, 소계의 ROW 들은 컬럼값에 NULL 값이 많은데, NVL(컬럼값, 0)을 활용해서 ORDER BY 를 하도록 한다. SELECT NVL(A.JOB_ID,0) AS JOB_ID, NVL(A.DEPARTMENT_ID,0) AS DEPARTMENT_ID, NVL(A.MANAGER_ID,0) AS MANAGER_ID, SUM(A.SALARY) FROM EMPLOYEES A GROUP BY ROLLUP(A.JOB_ID, A.DEPARTMENT_ID, A.MANAGER_ID) ORDER BY JOB_ID, DEPARTMENT_ID, MANAGER_ID; --4)총계, 소계가 되는 ROW를 구별하기 --총계가 되는 row는 첫번째 컬럼에 '총계'로 표시하고, 소계가 되는 row는 소계가 되는 컬럼 다음 컬럼에 '~~소계'로 표시 --위에서 살펴본 GROUPING_ID(JOB_ID, DEPARTMENT_ID, MANAGER_ID)를 활용한다. SELECT DECODE(GROUPING_ID, '7', '총 계', JOB_ID) AS JOB_ID , DECODE(GROUPING_ID, '3', '부서별 소계', DEPARTMENT_ID) AS DEPARTMENT_ID , DECODE(GROUPING_ID, '1', '관리자별 소계', MANAGER_ID) AS MANAGER_ID , SALARY FROM ( SELECT GROUPING_ID(A.JOB_ID, A.DEPARTMENT_ID, A.MANAGER_ID)AS GROUPING_ID , NVL(A.JOB_ID,0) AS JOB_ID , NVL(A.DEPARTMENT_ID,0) AS DEPARTMENT_ID , NVL(A.MANAGER_ID,0) AS MANAGER_ID , SUM(A.SALARY) AS SALARY FROM EMPLOYEES A GROUP BY ROLLUP(A.JOB_ID, A.DEPARTMENT_ID, A.MANAGER_ID) ORDER BY JOB_ID, DEPARTMENT_ID, MANAGER_ID); --5) 정리 --순서를 위해 NVL(컬럼명, 0)으로 했던 부분을 원복시키기 SELECT DECODE(DECODE(GROUPING_ID, '7', '총 계', JOB_ID), '0', '', DECODE(GROUPING_ID, '7', '총 계', JOB_ID)) AS JOB_ID , DECODE(DECODE(GROUPING_ID, '3', '부서별 소계', DEPARTMENT_ID), '0', '', DECODE(GROUPING_ID, '3', '부서별 소계', DEPARTMENT_ID)) AS DEPARTMENT_ID , DECODE(DECODE(GROUPING_ID, '1', '관리자별 소계', MANAGER_ID), '0', '', DECODE(GROUPING_ID, '1', '관리자별 소계', MANAGER_ID)) AS MANAGER_ID , SALARY FROM ( SELECT GROUPING_ID(A.JOB_ID, A.DEPARTMENT_ID, A.MANAGER_ID)AS GROUPING_ID , NVL(A.JOB_ID,0) AS JOB_ID , NVL(A.DEPARTMENT_ID,0) AS DEPARTMENT_ID , NVL(A.MANAGER_ID,0) AS MANAGER_ID , SUM(A.SALARY) AS SALARY FROM EMPLOYEES A GROUP BY ROLLUP(A.JOB_ID, A.DEPARTMENT_ID, A.MANAGER_ID) ORDER BY JOB_ID, DEPARTMENT_ID, MANAGER_ID); --OPTION)6) 소계 빼기 SELECT DECODE(DECODE(GROUPING_ID, '7', '총 계', JOB_ID), '0', '', DECODE(GROUPING_ID, '7', '총 계', JOB_ID)) AS JOB_ID , DECODE(DECODE(GROUPING_ID, '3', '부서별 소계', DEPARTMENT_ID), '0', '', DECODE(GROUPING_ID, '3', '부서별 소계', DEPARTMENT_ID)) AS DEPARTMENT_ID , DECODE(DECODE(GROUPING_ID, '1', '관리자별 소계', MANAGER_ID), '0', '', DECODE(GROUPING_ID, '1', '관리자별 소계', MANAGER_ID)) AS MANAGER_ID , SALARY FROM ( SELECT GROUPING_ID(A.JOB_ID, A.DEPARTMENT_ID, A.MANAGER_ID)AS GROUPING_ID , NVL(A.JOB_ID,0) AS JOB_ID , NVL(A.DEPARTMENT_ID,0) AS DEPARTMENT_ID , NVL(A.MANAGER_ID,0) AS MANAGER_ID , SUM(A.SALARY) AS SALARY FROM EMPLOYEES A GROUP BY ROLLUP(A.JOB_ID, A.DEPARTMENT_ID, A.MANAGER_ID) ORDER BY JOB_ID, DEPARTMENT_ID, MANAGER_ID) WHERE GROUPING_ID IN ('0', '7'); --(OPTION)7) 화면에는 JOB_TITLE(JOB_ID), DEPARTMENT_NAME(DEPARTMENT_ID), MANAGER_NAME(MANAGER_ID) 가 보이는데, ORDER BY 는 JOB_ID, DEPARTMENT_ID, MANAGER_ID 로 설정 --ㄱ. 총계, 소계를 구한 후, ㄴ. ID -> NAME 으로 변경, ㄷ. 총계, 소계 명칭 부여 SELECT DECODE(DECODE(GROUPING_ID, '7', '총 계', JOB_TITLE), '0', '', DECODE(GROUPING_ID, '7', '총 계', JOB_TITLE)) AS JOB_TITLE , DECODE(DECODE(GROUPING_ID, '3', '부서별 소계', DEPARTMENT_NAME), '0', '', DECODE(GROUPING_ID, '3', '부서별 소계', DEPARTMENT_NAME)) AS DEPARTMENT_NAME , DECODE(DECODE(GROUPING_ID, '1', '관리자별 소계', MANAGER_NAME), '0', '', DECODE(GROUPING_ID, '1', '관리자별 소계', MANAGER_NAME)) AS MANAGER_NAME , SALARY FROM ( SELECT GROUPING_ID , (SELECT B.JOB_TITLE || '(' || B.JOB_ID || ')' FROM JOBS B WHERE B.JOB_ID = A.JOB_ID) AS JOB_TITLE , (SELECT C.DEPARTMENT_NAME || '(' || C.DEPARTMENT_ID || ')' FROM DEPARTMENTS C WHERE C.DEPARTMENT_ID = A.DEPARTMENT_ID) AS DEPARTMENT_NAME , (SELECT D.FIRST_NAME || ' ' || D.LAST_NAME || '(' || D.EMPLOYEE_ID || ')' FROM EMPLOYEES D WHERE D.EMPLOYEE_ID = A.MANAGER_ID) AS MANAGER_NAME , SALARY FROM( SELECT GROUPING_ID(A.JOB_ID, A.DEPARTMENT_ID, A.MANAGER_ID) AS GROUPING_ID , NVL(A.JOB_ID,0) AS JOB_ID , NVL(A.DEPARTMENT_ID,0) AS DEPARTMENT_ID , NVL(A.MANAGER_ID,0) AS MANAGER_ID , SUM(A.SALARY) AS SALARY FROM EMPLOYEES A GROUP BY ROLLUP(A.JOB_ID, A.DEPARTMENT_ID, A.MANAGER_ID) ORDER BY JOB_ID, DEPARTMENT_ID, MANAGER_ID ) A );