SELECT LY.CTLR_MNGM_NMBR, LY.VDS_CTLR_ID, LY.LCTN, LY.DTCT_NMBR, LY.ISTL_LANE, LY.VDS_DTCT_NM, LX.TRFTYPE, DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(TOTTFVL, 0, '-', TOTTFVL)) AS TOTTFVL, DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(AVGTFVL, 0, '-', AVGTFVL)) AS AVGTFVLX, DECODE(NVL(L01,0)+NVL(L02,0)+NVL(L03,0)+NVL(L04,0)+NVL(L05,0)+NVL(L06,0)+NVL(L07,0)+ NVL(L08,0)+NVL(L09,0)+NVL(L10,0)+NVL(L11,0)+NVL(L12,0)+NVL(L13,0)+NVL(L14,0)+ NVL(L15,0)+NVL(L16,0)+NVL(L17,0)+NVL(L18,0)+NVL(L19,0)+NVL(L20,0)+NVL(L21,0)+ NVL(L22,0)+NVL(L23,0)+NVL(L24,0)+NVL(L25,0)+NVL(L26,0)+NVL(L27,0)+NVL(L28,0)+ NVL(L29,0)+NVL(L30,0)+NVL(L31,0), 0, '-', ROUND( (NVL(L01,0)+NVL(L02,0)+NVL(L03,0)+NVL(L04,0)+NVL(L05,0)+NVL(L06,0)+NVL(L07,0)+ NVL(L08,0)+NVL(L09,0)+NVL(L10,0)+NVL(L11,0)+NVL(L12,0)+NVL(L13,0)+NVL(L14,0)+ NVL(L15,0)+NVL(L16,0)+NVL(L17,0)+NVL(L18,0)+NVL(L19,0)+NVL(L20,0)+NVL(L21,0)+ NVL(L22,0)+NVL(L23,0)+NVL(L24,0)+NVL(L25,0)+NVL(L26,0)+NVL(L27,0)+NVL(L28,0)+ NVL(L29,0)+NVL(L30,0)+NVL(L31,0)) / (DECODE(L01,NULL,0,1)+DECODE(L02,NULL,0,1)+DECODE(L03,NULL,0,1)+ DECODE(L04,NULL,0,1)+DECODE(L05,NULL,0,1)+DECODE(L06,NULL,0,1)+ DECODE(L07,NULL,0,1)+DECODE(L08,NULL,0,1)+DECODE(L09,NULL,0,1)+ DECODE(L10,NULL,0,1)+DECODE(L11,NULL,0,1)+DECODE(L12,NULL,0,1)+ DECODE(L13,NULL,0,1)+DECODE(L14,NULL,0,1)+DECODE(L15,NULL,0,1)+ DECODE(L16,NULL,0,1)+DECODE(L17,NULL,0,1)+DECODE(L18,NULL,0,1)+ DECODE(L19,NULL,0,1)+DECODE(L20,NULL,0,1)+DECODE(L21,NULL,0,1)+ DECODE(L22,NULL,0,1)+DECODE(L23,NULL,0,1)+DECODE(L24,NULL,0,1)+ DECODE(L25,NULL,0,1)+DECODE(L26,NULL,0,1)+DECODE(L27,NULL,0,1)+ DECODE(L28,NULL,0,1)+DECODE(L29,NULL,0,1)+DECODE(L30,NULL,0,1)+ DECODE(L31,NULL,0,1)), 0)) AS AVGTFVL, DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L01, NULL, '-', L01)) AS C01, DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L02, NULL, '-', L02)) AS C02, DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L03, NULL, '-', L03)) AS C03, DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L04, NULL, '-', L04)) AS C04, DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L05, NULL, '-', L05)) AS C05, DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L06, NULL, '-', L06)) AS C06, DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L07, NULL, '-', L07)) AS C07, DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L08, NULL, '-', L08)) AS C08, DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L09, NULL, '-', L09)) AS C09, DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L10, NULL, '-', L10)) AS C10, DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L11, NULL, '-', L11)) AS C11, DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L12, NULL, '-', L12)) AS C12, DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L13, NULL, '-', L13)) AS C13, DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L14, NULL, '-', L14)) AS C14, DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L15, NULL, '-', L15)) AS C15, DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L16, NULL, '-', L16)) AS C16, DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L17, NULL, '-', L17)) AS C17, DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L18, NULL, '-', L18)) AS C18, DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L19, NULL, '-', L19)) AS C19, DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L20, NULL, '-', L20)) AS C20, DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L21, NULL, '-', L21)) AS C21, DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L22, NULL, '-', L22)) AS C22, DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L23, NULL, '-', L23)) AS C23, DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L24, NULL, '-', L24)) AS C24, DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L25, NULL, '-', L25)) AS C25, DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L26, NULL, '-', L26)) AS C26, DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L27, NULL, '-', L27)) AS C27, DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L28, NULL, '-', L28)) AS C28, DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L29, NULL, '-', L29)) AS C29, DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L30, NULL, '-', L30)) AS C30, DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L31, NULL, '-', L31)) AS C31 FROM ( SELECT GROUPING(DTCT_NMBR) AS GROUPING_DTCT_NMBR, GROUPING(ISTL_LANE) AS GROUPING_ISTL_LANE, GROUPING(TRFTYPE) AS GROUPING_TRFTYPE, CASE WHEN GROUPING(DTCT_NMBR ) = 1 THEN '검지기' ELSE DTCT_NMBR END AS GROUP_DTCT_NMBR, CASE WHEN GROUPING(ISTL_LANE) = 1 THEN '차로' ELSE ISTL_LANE END AS GROUP_ISTL_LANE, CASE WHEN GROUPING(TRFTYPE) = 1 THEN '소계' ELSE TRFTYPE END AS GROUP_TRFTYPE, DTCT_NMBR, ISTL_LANE, TRFTYPE, SUM(TFVL) TOTTFVL, ROUND(AVG(TFVL), 0) AVGTFVL, SUM(DECODE(STATDAY, '01', TFVL)) AS L01, SUM(DECODE(STATDAY, '02', TFVL)) AS L02, SUM(DECODE(STATDAY, '03', TFVL)) AS L03, SUM(DECODE(STATDAY, '04', TFVL)) AS L04, SUM(DECODE(STATDAY, '05', TFVL)) AS L05, SUM(DECODE(STATDAY, '06', TFVL)) AS L06, SUM(DECODE(STATDAY, '07', TFVL)) AS L07, SUM(DECODE(STATDAY, '08', TFVL)) AS L08, SUM(DECODE(STATDAY, '09', TFVL)) AS L09, SUM(DECODE(STATDAY, '10', TFVL)) AS L10, SUM(DECODE(STATDAY, '11', TFVL)) AS L11, SUM(DECODE(STATDAY, '12', TFVL)) AS L12, SUM(DECODE(STATDAY, '13', TFVL)) AS L13, SUM(DECODE(STATDAY, '14', TFVL)) AS L14, SUM(DECODE(STATDAY, '15', TFVL)) AS L15, SUM(DECODE(STATDAY, '16', TFVL)) AS L16, SUM(DECODE(STATDAY, '17', TFVL)) AS L17, SUM(DECODE(STATDAY, '18', TFVL)) AS L18, SUM(DECODE(STATDAY, '19', TFVL)) AS L19, SUM(DECODE(STATDAY, '20', TFVL)) AS L20, SUM(DECODE(STATDAY, '21', TFVL)) AS L21, SUM(DECODE(STATDAY, '22', TFVL)) AS L22, SUM(DECODE(STATDAY, '23', TFVL)) AS L23, SUM(DECODE(STATDAY, '24', TFVL)) AS L24, SUM(DECODE(STATDAY, '25', TFVL)) AS L25, SUM(DECODE(STATDAY, '26', TFVL)) AS L26, SUM(DECODE(STATDAY, '27', TFVL)) AS L27, SUM(DECODE(STATDAY, '28', TFVL)) AS L28, SUM(DECODE(STATDAY, '29', TFVL)) AS L29, SUM(DECODE(STATDAY, '30', TFVL)) AS L30, SUM(DECODE(STATDAY, '31', TFVL)) AS L31 FROM ( SELECT Y.DTCT_NMBR, Y.VDS_DTCT_NM, TO_CHAR(Y.ISTL_LANE) AS ISTL_LANE, X.TRFTYPE, X.STATDAY, X.TFVL FROM (SELECT /*+ INDEX(A IX_VDS_DTCT_HH_STAT_PK) */ 'DTCT' AS TRFTYPE, A.DTCT_NMBR AS DTCT_NMBR, SUBSTR(A.STAT_DT, 7, 2) AS STATDAY, A.TFVL AS TFVL FROM TB_VDS_DTCT_HH_STAT A WHERE 1=1 AND A.STAT_DT BETWEEN '20170201000000' AND '20170201235959' AND SUBSTR(A.STAT_DT, 9, 4) BETWEEN '0400' AND '0600' ) X, TB_VDS_DTCT_MSTR Y WHERE 1=1 AND Y.DEL_YN = 'N' AND Y.DTCT_NMBR = X.DTCT_NMBR ) GROUP BY ROLLUP (DTCT_NMBR, ISTL_LANE, TRFTYPE) HAVING GROUPING(TRFTYPE) = 0 ) LX, ( SELECT A.CTLR_MNGM_NMBR, A.VDS_CTLR_ID, A.LCTN, B.DTCT_NMBR, B.ISTL_LANE, B.VDS_DTCT_NM FROM TB_VDS_CTLR A, TB_VDS_DTCT_MSTR B WHERE 1=1 AND A.DEL_YN = 'N' AND B.DEL_YN = 'N' AND A.CTLR_MNGM_NMBR = B.CTLR_MNGM_NMBR ) LY WHERE LY.DTCT_NMBR = LX.DTCT_NMBR(+) ORDER BY LY.LCTN, LY.ISTL_LANE