SELECT SUM(COUNT(REQ_NO)) AS 응모자 ,
SUM(DECODE(WIN_YN,'Y',1,0)) AS 당첨자,
TRUNC(SUM(DECODE(WIN_YN,'Y',1,0))/SUM(COUNT(REQ_NO))*100,1) AS 통계,
(SELECT SUM(DECODE(COUNT(D.WIN_YN),1,1,NULL)) AS 당첨횟수1회
FROM EVT_BTPOINT_REQ D
WHERE D.WIN_YN = 'Y'
GROUP BY D.MEMBER_NO
ORDER BY D.MEMBER_NO
) 당첨횟수1회
FROM EVT_BTPOINT_REQ
GROUP BY MEMBER_NO,REQ_NO,WIN_YN;
감사합니다 그런데 당첨횟수를 구하는거라서 당첨1회횟수 당첨 2회된횟수 당첨 3회된횟수입니다
SELECT SUM(cnt) AS 응모자
, SUM(win) AS 당첨자
, TRUNC(SUM(win) / SUM(cnt) * 100, 1) AS 당첨_비율
, COUNT(DECODE(win, 1, 1)) AS 당첨횟수1회_회원수
, COUNT(DECODE(win, 2, 2)) AS 당첨횟수2회_회원수
, COUNT(DECODE(win, 3, 3)) AS 당첨횟수3회_회원수
FROM (SELECT COUNT(*) cnt
, COUNT(DECODE(win_yn, 'Y', 1)) win
FROM evt_btpoint_req
GROUP BY member_no
)
;
이런식으로하니 겹쳐서 총 당첨자랑 1회 2회 3회 합친거랑 안맞네요 ㅠㅠ