WITH TEMP AS (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 , TO_CHAR(SUM(reqCnt), '999,999') reqCnt , TO_CHAR(SUM(winCnt), '999,999') winCnt , RTRIM(TO_CHAR( RATIO_TO_REPORT(SUM(winCnt)) OVER(PARTITION BY GROUPING(age)) * 100 , 'fm990.99'), '.') || '%' winAvg , RTRIM(TO_CHAR(SUM(winCnt) / SUM(reqCnt) * 100, 'fm990.99'), '.') || '%' ageAvg FROM (SELECT age , evt_req_cnt reqCnt , evt_win_cnt winCnt FROM summary_btpoint UNION ALL SELECT age , evt_req_cnt1 + evt_req_cnt2 + evt_req_cnt3 reqCnt , evt_win_cnt1 + evt_win_cnt2 + evt_win_cnt3 winCnt FROM summary_aprilbox UNION ALL SELECT age , evt_req_cnt reqCnt , evt_win_cnt winCnt FROM summary_scratch )a GROUP BY (age))SELECT areaInfo,reqCnt,winCnt,winAvg,ageAvg FROM TEMP ; 결과가 60세 이상 37,696 975 4.17% 2.59% 60세 이상 2,418 50 0.21% 2.07% 25세~29세 2,461 67 0.29% 2.72% 15세~19세 20 8 0.03% 40% 60세 이상 20 5 0.02% 25% 40세~49세 9,278 43 0.18% 0.46% 15세 미만 73,562 3,562 15.25% 4.84% 40세~49세 27,541 2,574 11.02% 9.35% 60세 이상 4,493 50 0.21% 1.11% 25세~29세 2,623 116 0.5% 4.42% 50세~59세 1,751 36 0.15% 2.06% 50세~59세 5,635 72 0.31% 1.28% 나이별로 그룹으로 묵고 그룹핑해서 맨위에 총응모자 당첨자 비율 보이게 하고싶어요 도와주세요
SELECT NVL(age, '총인원') ageInfo , TO_CHAR(SUM(reqCnt), '999,999') reqCnt , TO_CHAR(SUM(winCnt), '999,999') winCnt , RTRIM(TO_CHAR( RATIO_TO_REPORT(SUM(winCnt)) OVER(PARTITION BY GROUPING(age)) * 100 , 'fm990.99'), '.') || '%' winAvg , RTRIM(TO_CHAR(SUM(winCnt) / SUM(reqCnt) * 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 age , reqCnt , winCnt FROM (SELECT age , evt_req_cnt reqCnt , evt_win_cnt winCnt FROM summary_btpoint UNION ALL SELECT age , evt_req_cnt1 + evt_req_cnt2 + evt_req_cnt3 reqCnt , evt_win_cnt1 + evt_win_cnt2 + evt_win_cnt3 winCnt FROM summary_aprilbox UNION ALL SELECT age , evt_req_cnt reqCnt , evt_win_cnt winCnt FROM summary_scratch ) a ) a GROUP BY ROLLUP(age) ORDER BY a.age NULLS FIRST ;