SELECT CASE WHEN age < 15 THEN '15세 미만' WHEN age >= 15 AND age < 20 THEN '15세~19세' WHEN age >= 20 AND age < 25 THEN '20세~24세' WHEN age >= 25 AND age < 30 THEN '25세~29세' WHEN age >= 30 AND age < 35 THEN '30세~34세' WHEN age >= 35 AND age < 40 THEN '35세~39세' WHEN age >= 40 AND age < 50 THEN '40세~49세' WHEN age >= 50 AND age < 60 THEN '50세~59세' WHEN age >= 60 THEN '60세 이상' END AS areaInfo ,SUM(EVT_REQ_CNT) reqCnt ,SUM(EVT_WIN_CNT) winCnt , CONCAT(TRIM('.' FROM TO_CHAR(ROUND((SUM(EVT_WIN_CNT)/SUM(EVT_REQ_CNT))*100,2),'fm990.99')),'%') ageAvg FROM SUMMARY_BTPOINT GROUP BY CASE WHEN age < 15 THEN '15세 미만' WHEN age >= 15 AND age < 20 THEN '15세~19세' WHEN age >= 20 AND age < 25 THEN '20세~24세' WHEN age >= 25 AND age < 30 THEN '25세~29세' WHEN age >= 30 AND age < 35 THEN '30세~34세' WHEN age >= 35 AND age < 40 THEN '35세~39세' WHEN age >= 40 AND age < 50 THEN '40세~49세' WHEN age >= 50 AND age < 60 THEN '50세~59세' WHEN age >= 60 THEN '60세 이상' END 여기서 롤업해서 응모자 합계랑 당첨자 합계를 맨위에 보여주고싶어요 헬프미 ㅜㅜ
계속 개선 쿼리, 간결한 쿼리 알려드려도. 기존 쿼리로 다시 질문하시네요.
SELECT NVL(areaInfo, '합계') areaInfo , SUM(evt_req_cnt) reqCnt , SUM(evt_win_cnt) winCnt , RTRIM(TO_CHAR(SUM(evt_win_cnt) / SUM(evt_req_cnt) * 100, 'fm990.99'), '.') || '%' ageAvg FROM (SELECT CASE WHEN age < 15 THEN '15세 미만' WHEN age >= 15 AND age < 20 THEN '15세~19세' WHEN age >= 20 AND age < 25 THEN '20세~24세' WHEN age >= 25 AND age < 30 THEN '25세~29세' WHEN age >= 30 AND age < 35 THEN '30세~34세' WHEN age >= 35 AND age < 40 THEN '35세~39세' WHEN age >= 40 AND age < 50 THEN '40세~49세' WHEN age >= 50 AND age < 60 THEN '50세~59세' WHEN age >= 60 THEN '60세 이상' END AS areaInfo , evt_req_cnt , evt_win_cnt FROM summary_btpoint ) a GROUP BY ROLLUP(areaInfo) ORDER BY a.areaInfo NULLS FIRST ;