데이터베이스 질문과 답변
by 스티브1조 [2018.03.05 22:02:15]
SELECT NVL(ageInfo, '총 당첨자수') ageInfo
, nvl(SUM(DECODE(gift_cd, '170013935', evt_win_cnt)),0) s1
, nvl(SUM(DECODE(gift_cd, '110650627', evt_win_cnt)),0) s2
, nvl(SUM(DECODE(gift_cd, '111650343', evt_win_cnt)),0) s3
, nvl(SUM(DECODE(gift_cd, '111971655', evt_win_cnt)),0) s4
, nvl(SUM(DECODE(gift_cd, '111130402', evt_win_cnt)),0) s5
, nvl(SUM(DECODE(gift_cd, '111072037', evt_win_cnt)),0) s6
, nvl(SUM(DECODE(gift_cd, '111170102', evt_win_cnt)),0) s7
, 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 area = '서울' THEN area
WHEN area = '경기' THEN area
WHEN area = '부산' THEN area
WHEN area = '강원' THEN area
WHEN area = '충남' THEN area
END AS ageInfo
FROM summary_aprilday
) a
GROUP BY ROLLUP(ageInfo)
ORDER BY a.ageInfo NULLS FIRST
;
데이터가 서울 경기만있어요 나머지는데이터가 없는데 혹시 0이라도 결과값을 표기하고싶은데 안나오네요 방법좀 ㅜㅜㅜ
WITH code AS
(
-- 기준 코드 : 코드테이블을 이용하면 좋음 --
SELECT 0 rn, '총 당첨자수' gb FROM dual
UNION ALL SELECT 1, '서울' FROM dual
UNION ALL SELECT 2, '경기' FROM dual
UNION ALL SELECT 3, '부산' FROM dual
UNION ALL SELECT 4, '강원' FROM dual
UNION ALL SELECT 5, '충남' FROM dual
)
, data AS
(
-- 집계 --
SELECT NVL(gb, '총 당첨자수') gb
, SUM(DECODE(cd, '170013935', cnt)) s1
, SUM(DECODE(cd, '110650627', cnt)) s2
, SUM(DECODE(cd, '111650343', cnt)) s3
, SUM(DECODE(cd, '111971655', cnt)) s4
, SUM(DECODE(cd, '111130402', cnt)) s5
, SUM(DECODE(cd, '111072037', cnt)) s6
, SUM(DECODE(cd, '111170102', cnt)) s7
, SUM(cnt) totalSum
, RTRIM(TO_CHAR(
RATIO_TO_REPORT(SUM(cnt)) OVER(PARTITION BY GROUPING(gb)) * 100
, 'fm990.99'), '.') || '%' totalRat
FROM (SELECT area gb -- 세로행기준
, gift_cd cd -- 가로열기준
, evt_win_cnt cnt -- 집계항목
FROM summary_aprilday
) a
GROUP BY ROLLUP(gb)
)
SELECT a.gb
, NVL(b.s1, 0) s1
, NVL(b.s2, 0) s2
, NVL(b.s3, 0) s3
, NVL(b.s4, 0) s4
, NVL(b.s5, 0) s5
, NVL(b.s6, 0) s6
, NVL(b.s7, 0) s7
, NVL(b.totalSum, 0 ) totalSum
, NVL(b.totalRat, '0%') totalRat
FROM code a
LEFT OUTER JOIN data b
ON a.gb = b.gb
ORDER BY a.rn
;
) 버튼을 클릭하여 작성 하시면 됩니다.