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

Programming Practice/SQL

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

효돌프 2014. 9. 13. 19:47

금액과 관련된 웹 화면에서 총계, 소계를 구해서 보여줘야 할 때가 많다.
아래와 같은 방식으로 보여주면 된다.

--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
      );