오늘도 도와주세요 0 2 209

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]

죄송합니다  ㅡㅜ

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입