SELECT a.SCRATCH_GIFT_NM AS 경품명,
a.GIFT_READY_CNT AS 전체경품수량,
NVL(B.GIFT_WIN_CNT,0) AS 당첨수량,
a.GIFT_READY_CNT - NVL(B.GIFT_WIN_CNT, 0) AS 남은수량
FROM EVT_SCRATCH_GIFT a
, (SELECT SCRATCH_GIFT_CD
, COUNT(*) gift_win_cnt
FROM EVT_SCRATCH_WIN
GROUP BY SCRATCH_GIFT_CD
) b
WHERE A.SCRATCH_GIFT_CD = B.SCRATCH_GIFT_CD(+)
ORDER BY a.GIFT_READY_CNT
여기서 롤업해서 전체경품수량과 전체 당첨수량 남은 전체 남은 수량을 뽑구싶어요 도와주세요 ㅠ
SELECT "경품명", SUM ("전체경품수량") "전체경품수량", SUM ("당첨수량") "당첨수량", SUM ("남은수량") "남은수량" FROM (SELECT A.SCRATCH_GIFT_NM AS 경품명 , A.GIFT_READY_CNT AS 전체경품수량 , NVL (B.GIFT_WIN_CNT, 0) AS 당첨수량 , A.GIFT_READY_CNT - NVL (B.GIFT_WIN_CNT, 0) AS 남은수량 FROM EVT_SCRATCH_GIFT A , (SELECT SCRATCH_GIFT_CD, COUNT (*) GIFT_WIN_CNT FROM EVT_SCRATCH_WIN GROUP BY SCRATCH_GIFT_CD) B WHERE A.SCRATCH_GIFT_CD = B.SCRATCH_GIFT_CD(+)) GROUP BY ROLLUP ("경품명")