ITS-OP/SRC/SQL/교통정보 수집결과 분석.sql

SELECT PRCN_DT, SUM(OPER01) AS OPER01, SUM(UTIS01) AS UTIS01, SUM(UTIS02) AS UTIS02, SUM(UTIS26) AS UTIS26
  FROM (SELECT PRCN_DT, 
               DECODE(CLCT_SYST_CD, 'OPER01', CLCT_NUM, 0) AS OPER01, 
               DECODE(CLCT_SYST_CD, 'UTIS01', CLCT_NUM, 0) AS UTIS01, 
               DECODE(CLCT_SYST_CD, 'UTIS02', CLCT_NUM, 0) AS UTIS02, 
               DECODE(CLCT_SYST_CD, 'UTIS26', CLCT_NUM, 0) AS UTIS26 
          FROM TB_CLCT_SYST_STTS_HS
         WHERE PRCN_DT BETWEEN '20140619000000' 
                           AND '20140619235959' 
           AND CLCT_SYST_CD IN ('OPER01', 'UTIS01', 'UTIS02', 'UTIS26')
       )
 GROUP BY PRCN_DT
 ORDER BY PRCN_DT




/*
SELECT * FROM
(
SELECT PRCN_DT, CLCT_SYST_CD, CLCT_NUM 
  FROM TB_CLCT_SYST_STTS_HS
 WHERE PRCN_DT BETWEEN '20140619000000' 
                   AND '20140619235959' 
   AND CLCT_SYST_CD IN ('OPER01', 'UTIS01', 'UTIS02', 'UTIS26')
)
PIVOT
(
    SUM(CLCT_NUM)
    FOR CLCT_SYST_CD IN (
      'OPER01' AS OPER01, 
      'UTIS01' AS UTIS01, 
      'UTIS02' AS UTIS02, 
      'UTIS26' AS UTIS26)
 )
*/