ROLLUP을 이용한 부서별 상위계정과목 집계 SQL

부서별 상위계정코드로 집계하는 SQL

WITH TEST /* SAMPLE DATA */
AS
(
    SELECT '10' AS DEPT_CODE, '110' AS ACCT_CODE, '100' AS UP_ACCT_CODE, 100 AS USE_AMT
     UNION ALL
    SELECT '10' AS DEPT_CODE, '110' AS ACCT_CODE, '100' AS UP_ACCT_CODE, 100 AS USE_AMT
     UNION ALL
    SELECT '20' AS DEPT_CODE, '120' AS ACCT_CODE, '100' AS UP_ACCT_CODE, 200 AS USE_AMT
     UNION ALL
    SELECT '10' AS DEPT_CODE, '120' AS ACCT_CODE, '100' AS UP_ACCT_CODE, 300 AS USE_AMT
     UNION ALL
    SELECT '20' AS DEPT_CODE, '120' AS ACCT_CODE, '100' AS UP_ACCT_CODE, 100 AS USE_AMT
     UNION ALL
    SELECT '10' AS DEPT_CODE, '120' AS ACCT_CODE, '100' AS UP_ACCT_CODE, 200 AS USE_AMT
     UNION ALL
    SELECT '10' AS DEPT_CODE, '210' AS ACCT_CODE, '200' AS UP_ACCT_CODE, 300 AS USE_AMT
     UNION ALL
    SELECT '30' AS DEPT_CODE, '220' AS ACCT_CODE, '200' AS UP_ACCT_CODE, 500 AS USE_AMT
     UNION ALL
    SELECT '10' AS DEPT_CODE, '230' AS ACCT_CODE, '200' AS UP_ACCT_CODE, 100 AS USE_AMT
     UNION ALL
    SELECT '10' AS DEPT_CODE, '230' AS ACCT_CODE, '200' AS UP_ACCT_CODE, 200 AS USE_AMT
     UNION ALL
    SELECT '30' AS DEPT_CODE, '310' AS ACCT_CODE, '300' AS UP_ACCT_CODE, 300 AS USE_AMT
     UNION ALL
    SELECT '30' AS DEPT_CODE, '310' AS ACCT_CODE, '300' AS UP_ACCT_CODE, 400 AS USE_AMT
     UNION ALL
    SELECT '30' AS DEPT_CODE, '310' AS ACCT_CODE, '300' AS UP_ACCT_CODE, 500 AS USE_AMT
)
/* SQL */
SELECT A.DEPT_CODE
 
     , CASE WHEN GROUPING(A.ACCT_CODE) = 1 THEN A.UP_ACCT_CODE 
                                           ELSE A.ACCT_CODE
       END                 AS ACCT_CODE
 
     , SUM(A.USE_AMT)      AS USE_AMT
     , GROUPING(ACCT_CODE) AS IS_SUM
  FROM (
        SELECT A.DEPT_CODE, A.ACCT_CODE, A.UP_ACCT_CODE, A.USE_AMT
             , ROW_NUMBER() OVER(ORDER BY A.ACCT_CODE ASC) AS RNO
          FROM TEST A
       ) A
 GROUP BY A.DEPT_CODE, A.UP_ACCT_CODE, A.ACCT_CODE, RNO
  WITH ROLLUP
HAVING GROUPING(A.UP_ACCT_CODE) = 0 AND GROUPING(A.RNO) = GROUPING(A.ACCT_CODE)
 ORDER BY A.DEPT_CODE, A.UP_ACCT_CODE, GROUPING(A.ACCT_CODE) DESC

[RESULT]