데이터베이스 질문과 답변
오늘도 도와주세요
0
2
618
by 스티브1조 [2018.03.08 14:51:27]
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
여기서 롤업해서 응모자 합계랑 당첨자 합계를 맨위에 보여주고싶어요 헬프미 ㅜㅜ
by 마농
[2018.03.08 15:43:00]
계속 개선 쿼리, 간결한 쿼리 알려드려도. 기존 쿼리로 다시 질문하시네요.
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
;
by 스티브1조
[2018.03.08 17:44:32]
죄송합니다 ㅡㅜ
) 버튼을 클릭하여 작성 하시면 됩니다.