SELECT NVL(ageInfo, '총 응모자수') ageInfo
, nvl(SUM(DECODE(gift_cd, 'SMEG냉장고', evt_req_cnt)),0) s1
, nvl(SUM(DECODE(gift_cd, 'LG트롬스타일러', evt_req_cnt)),0) s2
, nvl(SUM(DECODE(gift_cd, '고프로카메라', evt_req_cnt)),0) s3
, nvl(SUM(DECODE(gift_cd, '발뮤다공기청정기', evt_req_cnt)),0) s4
, nvl(SUM(DECODE(gift_cd, '다이슨헤어드라이기', evt_req_cnt)),0) s5
, nvl(SUM(DECODE(gift_cd, '마샬스피커', evt_req_cnt)),0) s6
, nvl(SUM(DECODE(gift_cd, '세븐라이너다리마시지기', evt_req_cnt)),0) s7
, nvl(SUM(DECODE(gift_cd, '드롱기커피포트', evt_req_cnt)),0) s8
, nvl(SUM(DECODE(gift_cd, '화장대', evt_req_cnt)),0) s9
, SUM(evt_req_cnt) totalSum
, CONCAT(TRIM('.' FROM TO_CHAR(ROUND(
RATIO_TO_REPORT(SUM(evt_req_cnt)) OVER(PARTITION BY GROUPING(ageInfo))
* 100, 2),'fm990.99')),'%') totalAvg
FROM (SELECT gift_cd
, evt_req_cnt
, 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 ageInfo
FROM summary_SCRATCH
--
) a
GROUP BY ROLLUP(ageInfo)
ORDER BY a.ageInfo NULLS FIRST
이쿼리랑
SELECT NVL(ageInfo, '총 당첨자수') ageInfo
, nvl(SUM(DECODE(gift_cd, 'SMEG냉장고', evt_win_cnt)),0) s1
, nvl(SUM(DECODE(gift_cd, 'LG트롬스타일러', evt_win_cnt)),0) s2
, nvl(SUM(DECODE(gift_cd, '고프로카메라', evt_win_cnt)),0) s3
, nvl(SUM(DECODE(gift_cd, '발뮤다공기청정기', evt_win_cnt)),0) s4
, nvl(SUM(DECODE(gift_cd, '다이슨헤어드라이기', evt_win_cnt)),0) s5
, nvl(SUM(DECODE(gift_cd, '마샬스피커', evt_win_cnt)),0) s6
, nvl(SUM(DECODE(gift_cd, '세븐라이너다리마시지기', evt_win_cnt)),0) s7
, nvl(SUM(DECODE(gift_cd, '드롱기커피포트', evt_win_cnt)),0) s8
, nvl(SUM(DECODE(gift_cd, '화장대', evt_win_cnt)),0) s9
, SUM(evt_win_cnt) totalSum
, CONCAT(TRIM('.' FROM TO_CHAR(ROUND(
RATIO_TO_REPORT(SUM(evt_win_cnt)) OVER(PARTITION BY GROUPING(ageInfo))
* 100, 2),'fm990.99')),'%') totalAvg
FROM (SELECT gift_cd
, evt_win_cnt
, 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 ageInfo
FROM summary_SCRATCH
--
) a
GROUP BY ROLLUP(ageInfo)
ORDER BY a.ageInfo NULLS FIRST
이쿼리를 유니온하고싶은데 안대네요 ㅠㅠㅠ 도와주세요
총 응모자수 8706 10351 9148 32718 14560 12743 20231 10262 13053 131772 100%
15세 미만 7931 5602 3324 25995 7491 8422 12756 7234 5039 83794 63.59%
20세~24세 518 4539 3440 4339 2233 3447 7151 475 3084 29226 22.18%
30세~34세 20 20 30 30 2333 352 77 30 20 2912 2.21%
40세~49세 237 190 2354 2354 2503 522 247 2523 4910 15840 12.02%
총 당첨자수 223 296 226 309 232 267 251 278 274 2356 100%
15세 미만 131 186 138 181 164 156 155 134 167 1412 59.93%
20세~24세 47 55 33 64 44 60 39 52 40 434 18.42%
30세~34세 7 15 20 29 6 18 18 33 2 148 6.28%
40세~49세 38 40 35 35 18 33 39 59 65 362 15.37%
당첨확률 10% 10% 10% 10% 10%10%10%10%10%10% 10% 10% 10% 10% <-- 맨아랫줄에 당첨확률까지요
WITH summary_scratch AS ( -- Sample -- SELECT 10 age, CAST('SMEG냉장고' AS VARCHAR(20)) gift_cd, 7931 evt_req_cnt, 131 evt_win_cnt FROM dual UNION ALL SELECT 20, 'SMEG냉장고', 518, 47 FROM dual UNION ALL SELECT 30, '세븐라이너다리마시지기', 510, 470 FROM dual ) SELECT gb , NVL(ageInfo, DECODE(gb, 'V1', '총 응모자수', 'V2', '총 당첨자수', 'V3', '당첨확률')) ageInfo , NVL(s1, 0) s1 , NVL(s2, 0) s2 , NVL(s3, 0) s3 , NVL(s4, 0) s4 , NVL(s5, 0) s5 , NVL(s6, 0) s6 , NVL(s7, 0) s7 , NVL(s8, 0) s8 , NVL(s9, 0) s9 , NVL(ss, 0) ss , DECODE(gb, 'V3', '' , RTRIM(TO_CHAR( RATIO_TO_REPORT(ss) OVER(PARTITION BY gb) * 100 * 2 , 'fm990.99'), '.') || '%' ) sr FROM (SELECT ageInfo , NVL(gift_cd, '합계') gift_cd , SUM(evt_req_cnt) v1 , SUM(evt_win_cnt) v2 , CASE WHEN GROUPING_ID(ageInfo, gift_cd) IN (2, 3) THEN ROUND(SUM(evt_win_cnt) / SUM(evt_req_cnt) * 100, 2) END v3 FROM (SELECT gift_cd , evt_req_cnt , evt_win_cnt , 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 ageInfo FROM summary_scratch ) GROUP BY CUBE(ageInfo, gift_cd) ) UNPIVOT (v FOR gb IN (v1, v2, v3)) PIVOT (SUM(v) FOR gift_cd IN ( 'SMEG냉장고' s1 , 'LG트롬스타일러' s2 , '고프로카메라' s3 , '발뮤다공기청정기' s4 , '다이슨헤어드라이기' s5 , '마샬스피커' s6 , '세븐라이너다리마시지기' s7 , '드롱기커피포트' s8 , '화장대' s9 , '합계' ss ) ) a ORDER BY a.gb, a.ageInfo NULLS FIRST ;