WITH TAB AS (
SELECT '2010-05-31' DT, '10' CODE,  100 AMT FROM DUAL UNION ALL
SELECT '2010-05-15' DT, '10' CODE,  30  AMT FROM DUAL UNION ALL
SELECT '2010-06-05' DT, '20' CODE,  10  AMT FROM DUAL UNION ALL
SELECT '2010-06-12' DT, '20' CODE,  200 AMT FROM DUAL UNION ALL
SELECT '2010-07-09' DT, '10' CODE,  100 AMT FROM DUAL UNION ALL
SELECT '2010-08-10' DT, '20' CODE,  300 AMT FROM DUAL
)

SELECT TO_NUMBER(SUBSTR(REPLACE(DT, '-', ''), 5, 2)) || '월'            MM
     , COALESCE(COUNT(CASE WHEN CODE = '10' THEN 1   ELSE NULL END), 0) CNT_10
     , COALESCE(SUM(CASE WHEN CODE   = '10' THEN AMT ELSE NULL END), 0) AMT_10
     , COALESCE(COUNT(CASE WHEN CODE = '20' THEN 1   ELSE NULL END), 0) CNT_20
     , COALESCE(SUM(CASE WHEN CODE   = '20' THEN AMT ELSE NULL END), 0) AMT_20
     , COUNT(*) ALL_CNT
     , SUM(AMT) ALL_AMT
  FROM TAB
 GROUP BY SUBSTR(REPLACE(DT, '-', ''), 5, 2)
 ORDER BY SUBSTR(REPLACE(DT, '-', ''), 5, 2)

지나가다님 답변 감사드립니다~

근데 제가 초보라 with tab as 부분이 이해가 잘안되는데요

select '2010-05-31' 에서 날짜 부분은 실제 입력된 날짜로 써야 하나요? 아니면 칼럼명으로 써야 되나요?

with tab as 부분 설명 부탁드려도 될까요?

with t as (
select '20100531' dt,  '10' dv,  100 amt from dual union all
select '20100515' dt,  '10' dv,  30  amt from dual union all
select '20100605' dt,  '20' dv,  10  amt from dual union all
select '20100612' dt,  '20' dv,  200 amt from dual union all
select '20100709' dt,  '10' dv,  100 amt from dual union all
select '20100810' dt,  '20' dv,  300 amt from dual
)
select substr(dt,1,6) mon
     , count(decode(dv, '10', dt    )) qty_10
     , sum  (decode(dv, '10', amt, 0)) amt_10
     , count(decode(dv, '20', dt    )) qty_20
     , sum  (decode(dv, '20', amt, 0)) amt_20
     , count(dt )                      qty_al
     , sum  (amt)                      amt_al
  from t
 group by substr(dt,1,6)
 order by 1

with as 는 사용하실 필요 없구요,.. .해당 테이블 사용하시면 됩니다..

아 부끄럽습니다...공부 많이 해야겠네요...

지나가다님, 知音(sunnylee72) 님 모두 답변 감사합니다.