데이터베이스 질문과 답변
SELECT
DECODE(BRANCH_CD,'','합계', BRANCH_CD) BRANCH_CD
, NVL(SUM(DECODE(MM1,'01',CNT)),0) AS M01
, NVL(SUM(DECODE(MM1,'02',CNT)),0) AS M02
, NVL(SUM(DECODE(MM1,'03',CNT)),0) AS M03
, NVL(SUM(DECODE(MM1,'04',CNT)),0) AS M04
, NVL(SUM(DECODE(MM1,'05',CNT)),0) AS M05
, NVL(SUM(DECODE(MM1,'06',CNT)),0) AS M06
, NVL(SUM(DECODE(MM1,'07',CNT)),0) AS M07
, NVL(SUM(DECODE(MM1,'08',CNT)),0) AS M08
, NVL(SUM(DECODE(MM1,'09',CNT)),0) AS M09
, NVL(SUM(DECODE(MM1,'10',CNT)),0) AS M10
, NVL(SUM(DECODE(MM1,'11',CNT)),0) AS M11
, NVL(SUM(DECODE(MM1,'12',CNT)),0) AS M12
, NVL(SUM(DECODE(MM1,'02',CNT)),0)
+ NVL(SUM(DECODE(MM1,'03',CNT)),0)
+ NVL(SUM(DECODE(MM1,'04',CNT)),0)
+ NVL(SUM(DECODE(MM1,'05',CNT)),0)
+ NVL(SUM(DECODE(MM1,'06',CNT)),0)
+ NVL(SUM(DECODE(MM1,'07',CNT)),0)
+ NVL(SUM(DECODE(MM1,'08',CNT)),0)
+ NVL(SUM(DECODE(MM1,'09',CNT)),0)
+ NVL(SUM(DECODE(MM1,'10',CNT)),0)
+ NVL(SUM(DECODE(MM1,'11',CNT)),0)
+ NVL(SUM(DECODE(MM1,'12',CNT)),0) AS TOTAL
FROM
(
SELECT BRANCH_CD
,COUNT(MEM_TY) AS CNT
,TO_CHAR(CDT,'YYYY') YEAR1
,TO_CHAR(CDT,'MM') MM1
,TO_CHAR(CDT,'DD') DD1
,TO_CHAR(CDT,'YYYY-MM-DD') CDT
, MEM_TY
FROM
TMB_MEMBER
WHERE 1=1
AND TO_CHAR(CDT,'YYYY') = '2017'
GROUP BY BRANCH_CD , CDT, MEM_TY
ORDER BY CDT ASC
)
GROUP BY ROLLUP( BRANCH_CD )
ORDER BY BRANCH_CD ASC
;
안녕하세요 첨부한 이미지처럼 월별로 나오게는 했는데
회원 타입별로 나눠져서 출력하는걸 모르겠어서 문의 드립니다
감사합니다 !
WITH tmb_member AS
(
SELECT '01' branch_cd, DATE '2017-01-01' cdt, 1 mem_ty FROM dual
UNION ALL SELECT '01', DATE '2017-01-01', 2 FROM dual
UNION ALL SELECT '01', DATE '2017-01-01', 2 FROM dual
UNION ALL SELECT '01', DATE '2017-01-01', 3 FROM dual
UNION ALL SELECT '01', DATE '2017-01-01', 3 FROM dual
UNION ALL SELECT '01', DATE '2017-01-01', 3 FROM dual
UNION ALL SELECT '02', DATE '2017-01-01', 1 FROM dual
UNION ALL SELECT '02', DATE '2017-01-01', 1 FROM dual
UNION ALL SELECT '02', DATE '2017-01-01', 1 FROM dual
UNION ALL SELECT '02', DATE '2017-01-01', 2 FROM dual
UNION ALL SELECT '02', DATE '2017-01-01', 2 FROM dual
UNION ALL SELECT '02', DATE '2017-01-01', 3 FROM dual
UNION ALL SELECT '01', DATE '2017-02-01', 1 FROM dual
UNION ALL SELECT '01', DATE '2017-02-01', 2 FROM dual
UNION ALL SELECT '01', DATE '2017-02-01', 3 FROM dual
UNION ALL SELECT '02', DATE '2017-02-01', 1 FROM dual
UNION ALL SELECT '02', DATE '2017-02-01', 2 FROM dual
UNION ALL SELECT '02', DATE '2017-02-01', 3 FROM dual
)
SELECT branch_cd
, COUNT(DECODE(gb, '101', 1)) 정회원_01
, COUNT(DECODE(gb, '201', 1)) 준회원_01
, COUNT(DECODE(gb, '301', 1)) 비회원_01
, COUNT(DECODE(gb, '102', 1)) 정회원_02
, COUNT(DECODE(gb, '202', 1)) 준회원_02
, COUNT(DECODE(gb, '302', 1)) 비회원_02
, COUNT(DECODE(gb, '103', 1)) 정회원_03
, COUNT(DECODE(gb, '203', 1)) 준회원_03
, COUNT(DECODE(gb, '303', 1)) 비회원_03
, COUNT(DECODE(gb, '104', 1)) 정회원_04
, COUNT(DECODE(gb, '204', 1)) 준회원_04
, COUNT(DECODE(gb, '304', 1)) 비회원_04
, COUNT(DECODE(gb, '105', 1)) 정회원_05
, COUNT(DECODE(gb, '205', 1)) 준회원_05
, COUNT(DECODE(gb, '305', 1)) 비회원_05
, COUNT(DECODE(gb, '106', 1)) 정회원_06
, COUNT(DECODE(gb, '206', 1)) 준회원_06
, COUNT(DECODE(gb, '306', 1)) 비회원_06
, COUNT(DECODE(gb, '107', 1)) 정회원_07
, COUNT(DECODE(gb, '207', 1)) 준회원_07
, COUNT(DECODE(gb, '307', 1)) 비회원_07
, COUNT(DECODE(gb, '108', 1)) 정회원_08
, COUNT(DECODE(gb, '208', 1)) 준회원_08
, COUNT(DECODE(gb, '308', 1)) 비회원_08
, COUNT(DECODE(gb, '109', 1)) 정회원_09
, COUNT(DECODE(gb, '209', 1)) 준회원_09
, COUNT(DECODE(gb, '309', 1)) 비회원_09
, COUNT(DECODE(gb, '110', 1)) 정회원_10
, COUNT(DECODE(gb, '210', 1)) 준회원_10
, COUNT(DECODE(gb, '310', 1)) 비회원_10
, COUNT(DECODE(gb, '111', 1)) 정회원_11
, COUNT(DECODE(gb, '211', 1)) 준회원_11
, COUNT(DECODE(gb, '311', 1)) 비회원_11
, COUNT(DECODE(gb, '112', 1)) 정회원_12
, COUNT(DECODE(gb, '212', 1)) 준회원_12
, COUNT(DECODE(gb, '312', 1)) 비회원_12
FROM (SELECT branch_cd
, mem_ty || TO_CHAR(cdt, 'mm') gb
FROM tmb_member
WHERE cdt >= TO_DATE('2017' || '01', 'yyyymm')
AND cdt < TO_DATE('2017' + 1 || '01', 'yyyymm')
)
GROUP BY ROLLUP(branch_cd)
;
감사합니다! 계속 헤맸었는데 답변 주셔서 참고해서 공부 더해볼게요 감사합니다! 좋은하루 보내세요!
) 버튼을 클릭하여 작성 하시면 됩니다.