다음의 결과값을 날짜와 인덱스를 기준으로 grouping하여 시간을 합산하고 싶습니다.
hh:mm 형식의 값을 분으로 환산하여 합산하고 hh:mm으로 나타내고 싶은데 어떻게 할지 막막합니다 ㅠㅠ
날짜 인덱스 시간
2017-12-01 01 06:00
2017-12-01 02 10:00
2017-12-01 03 10:00
2017-12-01 04 10:00
2017-12-01 05 10:00
2017-12-01 06 10:00
2017-12-01 07 10:00
2017-12-01 08 01:12
2017-12-01 08 09:28
2017-12-01 09 09:45
2017-12-01 10 09:45
2017-12-01 11 06:00
2017-12-01 12 09:00
2017-12-01 13 10:00
[원하는 결과값]
날짜 인덱스 시간
2017-12-01 01 06:00
2017-12-01 02 10:00
2017-12-01 03 10:00
2017-12-01 04 10:00
2017-12-01 05 10:00
2017-12-01 06 10:00
2017-12-01 07 10:00
2017-12-01 08 10:40
2017-12-01 09 09:45
2017-12-01 10 09:45
2017-12-01 11 06:00
2017-12-01 12 09:00
2017-12-01 13 10:00
-- 마농님은 아니지만... WITH T AS ( SELECT '2017-12-01' DT , '01' IDX , '06:00' HH FROM DUAL UNION ALL SELECT '2017-12-01' , '02' , '10:00' FROM DUAL UNION ALL SELECT '2017-12-01' , '03' , '10:00' FROM DUAL UNION ALL SELECT '2017-12-01' , '04' , '10:00' FROM DUAL UNION ALL SELECT '2017-12-01' , '05' , '10:00' FROM DUAL UNION ALL SELECT '2017-12-01' , '06' , '10:00' FROM DUAL UNION ALL SELECT '2017-12-01' , '07' , '10:00' FROM DUAL UNION ALL SELECT '2017-12-01' , '08' , '01:12' FROM DUAL UNION ALL SELECT '2017-12-01' , '08' , '09:28' FROM DUAL UNION ALL SELECT '2017-12-01' , '09' , '09:45' FROM DUAL UNION ALL SELECT '2017-12-01' , '10' , '09:45' FROM DUAL UNION ALL SELECT '2017-12-01' , '11' , '06:00' FROM DUAL UNION ALL SELECT '2017-12-01' , '12' , '09:00' FROM DUAL UNION ALL SELECT '2017-12-01' , '13' , '10:00' FROM DUAL ) SELECT DT , IDX , LPAD(TRUNC(HH/60 ),2,'0')|| ':' || LPAD(MOD(HH,60),2,'0') HH FROM ( SELECT DT , IDX , SUM(TO_NUMBER(SUBSTR(HH,1,2))) * 60 + SUM(TO_NUMBER(SUBSTR(HH,4,2))) HH FROM T GROUP BY DT , IDX ) ORDER BY DT , IDX