|
SELECT DECODE(ds_depth2, NULL, 'Total', ds_depth2),
sum( CASE SUBSTR(dte,1,6) WHEN '201203' THEN cnt ELSE 0 END) mm201203,
sum( CASE SUBSTR(dte,1,6) WHEN '201204' THEN cnt ELSE 0 END) mm201204,
sum( CASE SUBSTR(dte,1,6) WHEN '201205' THEN cnt ELSE 0 END) mm201205,
FROM
(SELECT to_char(a.finish_dtime,'yyyymmdd') dte,
h.voc_vehicle,
e.voc_code ,
e.ds_depth1,
ds_depth2,
e.ds_depth3 vw1 ,
count(*) cnt
FROM vqm_voc_org a
INNER JOIN gat_voccodemap d
ON a.cd_code = d.code
INNER JOIN gat_voccode e
ON d.voc_code = e.voc_code
INNER JOIN gat_vocvehiclemap g
ON a.car_code = g.seriescd
INNER JOIN gat_vocvehicle h
ON g.voc_vehicle = h.voc_vehicle
WHERE to_char(finish_dtime,'yyyymmdd') BETWEEN '20120301' AND '20120930'
/*일자*/
AND a.no_any IS NOT NULL
AND a.cd_media ='0010' -- 콜센터
AND h.voc_vehicle IN( 'T100' , 'T200' , 'T300' , 'T400' , 'T500' , 'T600' , 'T610' , 'T700' , 'T999' , 'V001' , 'V100' , 'V110' , 'V200' , 'V300' , 'V400' , 'V410' , 'V500' , 'V600' , 'V700' , 'V800' , 'V900' )
AND e.ds_depth1 ='Quality'
GROUP BY to_char(finish_dtime,'yyyymmdd'),
e.voc_code,
e.ds_depth1,
ds_depth2,
ds_depth3 ,
h.voc_vehicle
UNION ALL
SELECT to_char(a.finish_dtime,'yyyymmdd') dte,
h.voc_vehicle,
e.voc_code ,
e.ds_depth1,
ds_depth2,
e.ds_depth3 vw1 ,
count(*) web_cnt
FROM VQM_VOC_ORG a
INNER JOIN gat_voccodemap d
ON a.cd_code = d.code
INNER JOIN gat_voccode e
ON d.voc_code = e.voc_code
INNER JOIN gat_vocvehiclemap g
ON a.car_code = g.seriescd
INNER JOIN gat_vocvehicle h
ON g.voc_vehicle = h.voc_vehicle
WHERE to_char(finish_dtime,'yyyymmdd') BETWEEN '20120301' AND '20120930'
AND h.voc_vehicle IN( 'T100' , 'T200' , 'T300' , 'T400' , 'T500' , 'T600' , 'T610' , 'T700' , 'T999' , 'V001' , 'V100' , 'V110' , 'V200' , 'V300' , 'V400' , 'V410' , 'V500' , 'V600' , 'V700' , 'V800' , 'V900' )
AND a.cd_media ='0020' -- 웹 구분
AND e.ds_depth1 ='Quality'
GROUP BY to_char(finish_dtime,'yyyymmdd'),
h.voc_vehicle,
e.voc_code ,
e.ds_depth1,
ds_depth2,
ds_depth3
) ttmp1
GROUP BY ROLLUP(ds_depth2)
sum 하는 부분을 위에 쿼리 처럼 하드 코딩 하지 말고 자동으로 나오게 할 수 있을까요?
|
