가로로 SUM구하기

SUM(R_AMOUNT_SUM_1401_NR) AS R_AMOUNT_SUM_1401_NR

 

SUM(CASE WHEN T.ACNT_CODE = 'CD1' AND T.ASSETS_CL_CODE = '01' AND T.NREC = 1 THEN T.REQST_AMOUNT ELSE 0 END) AS R_AMOUNT_SUM_CD1_NR

 

=================================================================
WITH V_EU2010 AS (
 SELECT 
        A.FSYR_SE
      , A.ACCNUT_UNIT_CODE
      , A.FSSE_CODE         /* 회계구분코드 */
      , A.REQST_ODR_CODE    /* 신청차수코드 */
      , A.PURCHS_REQST_NO
      , A.BUDGET_REQST_NO   /* 예산신청번호 */
      , A.JOB_UNIT_CODE     /* 업무단위코드 */
      , A.REQST_DEPT_CODE   /* 신청부서코드 */
      , B.ASSETS_CL_CODE  /* 자산분류코드 */
      , B.THNG_CODE       /* 물품코드 */
      , B.REQST_AMOUNT    /* 신청금액 */
      , SUBSTR(A.TAXITM_CODE,1,4) AS ACNT_CODE   /* 계정코드 */
      , A.TAXITM_CODE     /* 세목코드 */
      , C.INCME_EXPNDTR_SE /* 수입지출구분 EG003 세입 01 세출 02 */
      , C.TAXITM_NM       /* 세목명 */
      , A.PROCESS_DATE      /* 처리일자 */
      , DECODE(A.PROCESS_DATE, NULL, 1, 0) AS NREC      /* 미접수 */
      , DECODE(A.PROCESS_DATE, NULL, 0, 1) AS REC       /* 접수 */
 FROM TABLE1 A /* 구매신청관리  */
 LEFT JOIN TABLE2 B /* 구매신청물품관리 */
                 ON A.FSYR_SE = B.FSYR_SE 
                AND A.ACCNUT_UNIT_CODE = B.ACCNUT_UNIT_CODE
                AND A.FSSE_CODE = B.FSSE_CODE
                AND A.REQST_ODR_CODE = B.REQST_ODR_CODE
                AND A.PURCHS_REQST_NO = B.PURCHS_REQST_NO
                AND A.ACCNUT_UNIT_CODE = nvl('02', '02')                                         
                AND A.FSYR_SE = 'YYYY'                          
                AND A.JOB_UNIT_CODE = '*********'    
                AND A.REQST_ODR_CODE = '####' 
 LEFT JOIN TABLE_3 C 
                  ON A.ACCNUT_UNIT_CODE = C.ACCNUT_UNIT_CODE
                 AND A.TAXITM_CODE = C.TAXITM_CODE  
                 AND A.ACCNUT_UNIT_CODE = nvl('02', '02')                                         
                 AND A.FSYR_SE = 'YYYY'                
WHERE 1=1 
  AND A.ACCNUT_UNIT_CODE = nvl('##', '02')                                         
  AND A.FSYR_SE = 'YYYY'                          
  AND A.JOB_UNIT_CODE = '*********'
  AND A.REQST_ODR_CODE = '####' 
)
SELECT 
      T2.REQST_DEPT_CODE
     , F_CB2000_S(T2.REQST_DEPT_CODE, '1') AS DEPT_NM 
     , SUM(R_AMOUNT_SUM_1401_NR) AS R_AMOUNT_SUM_1401_NR 
     , SUM(R_AMOUNT_SUM_1401_R) AS R_AMOUNT_SUM_1401_R 
     , SUM(R_AMOUNT_SUM_1402_NR) AS R_AMOUNT_SUM_1402_NR 
     , SUM(R_AMOUNT_SUM_1402_R) AS R_AMOUNT_SUM_1402_R 
     , SUM(R_AMOUNT_SUM_14)   AS R_AMOUNT_SUM_14
     , SUM(R_AMOUNT_SUM_1501_NR) AS R_AMOUNT_SUM_1501_NR
     , SUM(R_AMOUNT_SUM_1501_R) AS R_AMOUNT_SUM_1501_R
     , SUM(R_AMOUNT_SUM_1502_NR) AS R_AMOUNT_SUM_1502_NR
     , SUM(R_AMOUNT_SUM_1501_R) AS R_AMOUNT_SUM_1502_R
     , SUM(R_AMOUNT_SUM_15)   AS R_AMOUNT_SUM_15
     , (SUM(R_AMOUNT_SUM_14) + SUM(R_AMOUNT_SUM_15)) AS R_AMOUNT_SUM
FROM (     
    SELECT  T.FSYR_SE
          , T.ACCNUT_UNIT_CODE
          , T.FSSE_CODE
          , T.ACNT_CODE
          , T.ASSETS_CL_CODE
          , T.REQST_DEPT_CODE 
          , SUM(CASE WHEN T.ACNT_CODE = 'CD1' AND T.ASSETS_CL_CODE = '01' AND T.NREC = 1 THEN T.REQST_AMOUNT ELSE 0 END) AS R_AMOUNT_SUM_CD1_NR
          , SUM(CASE WHEN T.ACNT_CODE = 'CD1' AND T.ASSETS_CL_CODE = '01' AND T.REC = 1  THEN T.REQST_AMOUNT ELSE 0 END) AS R_AMOUNT_SUM_CD1_R
          , SUM(CASE WHEN T.ACNT_CODE = 'CD2' AND T.ASSETS_CL_CODE = '02' AND T.NREC = 1 THEN T.REQST_AMOUNT ELSE 0 END) AS R_AMOUNT_SUM_CD2_NR
          , SUM(CASE WHEN T.ACNT_CODE = 'CD2' AND T.ASSETS_CL_CODE = '02' AND T.REC = 1  THEN T.REQST_AMOUNT ELSE 0 END) AS R_AMOUNT_SUM_CD2_R
          , SUM(CASE WHEN T.ACNT_CODE = 'CD' THEN T.REQST_AMOUNT ELSE 0 END) AS R_AMOUNT_SUM_14
          , SUM(CASE WHEN T.ACNT_CODE = 'CD15' AND T.ASSETS_CL_CODE = '01' AND T.NREC = 1 THEN T.REQST_AMOUNT ELSE 0 END) AS R_AMOUNT_SUM_1501_NR
          , SUM(CASE WHEN T.ACNT_CODE = 'CD15' AND T.ASSETS_CL_CODE = '01' AND T.REC = 1  THEN T.REQST_AMOUNT ELSE 0 END) AS R_AMOUNT_SUM_CD151_R
          , SUM(CASE WHEN T.ACNT_CODE = 'CD15' AND T.ASSETS_CL_CODE = '02' AND T.NREC = 1 THEN T.REQST_AMOUNT ELSE 0 END) AS R_AMOUNT_SUM_CD152_NR
          , SUM(CASE WHEN T.ACNT_CODE = 'CD15' AND T.ASSETS_CL_CODE = '02' AND T.REC = 1  THEN T.REQST_AMOUNT ELSE 0 END) AS R_AMOUNT_SUM_CD15_R
          , SUM(CASE WHEN T.ACNT_CODE = 'CD15' THEN T.REQST_AMOUNT ELSE 0 END) AS R_AMOUNT_SUM_15
    FROM V_EU2010 T
    GROUP BY T.FSYR_SE, T.ACCNUT_UNIT_CODE, T.FSSE_CODE, T.ACNT_CODE, T.ASSETS_CL_CODE, T.REQST_DEPT_CODE
) T2
GROUP BY T2.REQST_DEPT_CODE, F_CB2000_S(T2.REQST_DEPT_CODE, '1')
;