데이터베이스 질문과 답변
by 데미소다a [2015.01.27 14:58:49]
A Table
SELECT '2014-01-01' dates, '영업1팀' dept FROM dual UNION ALL
SELECT '2014-01-01' dates, '영업2팀' dept FROM dual UNION ALL
SELECT '2014-01-02' dates, '영업1팀' dept FROM dual UNION ALL
SELECT '2014-01-03' dates, '영업1팀' dept FROM dual UNION ALL
SELECT '2014-02-04' dates, '영업2팀' dept FROM dual UNION ALL
SELECT '2014-02-05' dates, '영업1팀' dept FROM dual UNION ALL
SELECT '2014-02-06' dates, '영업2팀' dept FROM dual UNION ALL
SELECT '2014-03-01' dates, '영업1팀' dept FROM dual UNION ALL
SELECT '2014-03-02' dates, '영업2팀' dept FROM dual
B Table
SELECT '2014-01-05' dates, '영업1팀' dept FROM dual UNION ALL
SELECT '2014-01-07' dates, '영업2팀' dept FROM dual UNION ALL
SELECT '2014-01-11' dates, '영업1팀' dept FROM dual UNION ALL
SELECT '2014-01-23' dates, '영업1팀' dept FROM dual UNION ALL
SELECT '2014-02-09' dates, '영업2팀' dept FROM dual UNION ALL
SELECT '2014-02-14' dates, '영업1팀' dept FROM dual UNION ALL
SELECT '2014-02-22' dates, '영업2팀' dept FROM dual UNION ALL
SELECT '2014-03-05' dates, '영업1팀' dept FROM dual UNION ALL
SELECT '2014-03-19' dates, '영업2팀' dept FROM dual
위와 같은 형식으로 A ~ E 테이블 까지 있는데요
위 데이터를 월별 부서간 실적 건수 와 부서별 월간 실적 건수 를 출력하고 싶은데요.
도저히 감이 안잡혀서요..
PIVOT을 사용하면 된것 같아 이것저것 시도를 해보았는데, 정확한 현황이 출력이 안되더라구요..
혹시 방법을 아시는분 계실까요?
원하는 결과표를 보여주세요.
A B C D E
1월 영업1팀 5 4 6 2 4
영업2팀 3 5 2 1 5
2월 영업1팀 5 4 6 2 4
영업2팀 3 5 2 1 5
3월 영업1팀 5 4 6 2 4
영업2팀 3 5 2 1 5
-----------------------------------------------------------
1월 2월 3월
1팀 2팀 1팀 2팀 1팀 2팀
A 5 3 2 5 6 7
B 5 3 2 5 6 7
C 5 3 2 5 6 7
D 5 3 2 5 6 7
E 5 3 2 5 6 7
두가지 형식입니다ㅠ
A, B, C, D, E 는 테이블 입니다.
SELECT SUBSTR(dates, 6, 2) mm
, dept
, COUNT(DECODE(gb, 'A', 1)) a
, COUNT(DECODE(gb, 'B', 1)) b
, COUNT(DECODE(gb, 'C', 1)) c
, COUNT(DECODE(gb, 'D', 1)) d
, COUNT(DECODE(gb, 'E', 1)) e
, COUNT(*) tot
FROM (SELECT 'A' gb, dates, dept FROM a
UNION ALL SELECT 'B', dates, dept FROM b
UNION ALL SELECT 'C', dates, dept FROM c
UNION ALL SELECT 'D', dates, dept FROM d
UNION ALL SELECT 'E', dates, dept FROM e
)
WHERE dates LIKE '2014'||'%'
GROUP BY SUBSTR(dates, 6, 2), dept
ORDER BY ym, dept
;
SELECT gb
, COUNT(CASE WHEN dept = '영업1팀' AND SUBSTR(dates, 6, 2) = '01' THEN 1 END) "영업1팀_01월"
, COUNT(CASE WHEN dept = '영업2팀' AND SUBSTR(dates, 6, 2) = '01' THEN 1 END) "영업2팀_01월"
, COUNT(CASE WHEN dept = '영업1팀' AND SUBSTR(dates, 6, 2) = '02' THEN 1 END) "영업1팀_02월"
, COUNT(CASE WHEN dept = '영업2팀' AND SUBSTR(dates, 6, 2) = '02' THEN 1 END) "영업2팀_02월"
, COUNT(CASE WHEN dept = '영업1팀' AND SUBSTR(dates, 6, 2) = '03' THEN 1 END) "영업1팀_03월"
, COUNT(CASE WHEN dept = '영업2팀' AND SUBSTR(dates, 6, 2) = '03' THEN 1 END) "영업2팀_03월"
, COUNT(CASE WHEN dept = '영업1팀' THEN 1 END) "영업1팀_소계"
, COUNT(CASE WHEN dept = '영업2팀' THEN 1 END) "영업2팀_소계"
, COUNT(*) 합계
FROM (SELECT 'A' gb, dates, dept FROM a
UNION ALL SELECT 'B', dates, dept FROM b
UNION ALL SELECT 'C', dates, dept FROM c
UNION ALL SELECT 'D', dates, dept FROM d
UNION ALL SELECT 'E', dates, dept FROM e
)
WHERE dates LIKE '2014'||'%'
AND dept IN ('영업1팀', '영업2팀')
GROUP BY gb
ORDER BY gb
;
이런 방법이 있었군요
전 꼭 PIVOT을 사용해야 하는줄 알았는데ㅋ
감사합니다~
) 버튼을 클릭하여 작성 하시면 됩니다.