연도별 월별 데이터 count 조회 select문 쿼리

* T2.REG_YMD는 string타입 데이터로 맨앞 네글자는 연도, 그 다음 두글자는 월을 나타냄
* 연도별 각 월에 해당하는 해당 부서 월 입사자의 수를 count

SELECT
    SUBSTR(T2.REG_YMD, 1, 4) AS YEAR,
    SUM(CASE WHEN SUBSTR(T2.REG_YMD, 5, 2) = '01' AND T1.DEPT_CD = '1' THEN 1 ELSE 0 END) AS ES01,
    SUM(CASE WHEN SUBSTR(T2.REG_YMD, 5, 2) = '02' AND T1.DEPT_CD = '1' THEN 1 ELSE 0 END) AS ES02,
    SUM(CASE WHEN SUBSTR(T2.REG_YMD, 5, 2) = '03' AND T1.DEPT_CD = '1' THEN 1 ELSE 0 END) AS ES03,
    SUM(CASE WHEN SUBSTR(T2.REG_YMD, 5, 2) = '04' AND T1.DEPT_CD = '1' THEN 1 ELSE 0 END) AS ES04,
    ...
    SUM(CASE WHEN SUBSTR(T2.REG_YMD, 5, 2) = '12' AND T1.DEPT_CD = '1' THEN 1 ELSE 0 END) AS ES12,
    COUNT(CASE WHEN T1.DEPT_CD = '1' THEN 1 ELSE NULL END) AS ES_ALL,
    SUM(CASE WHEN SUBSTR(T2.REG_YMD, 5, 2) = '01' AND T1.DEPT_CD = '2' THEN 1 ELSE 0 END) AS SS01,
    SUM(CASE WHEN SUBSTR(T2.REG_YMD, 5, 2) = '02' AND T1.DEPT_CD = '2' THEN 1 ELSE 0 END) AS SS02,
    SUM(CASE WHEN SUBSTR(T2.REG_YMD, 5, 2) = '03' AND T1.DEPT_CD = '2' THEN 1 ELSE 0 END) AS SS03,
    SUM(CASE WHEN SUBSTR(T2.REG_YMD, 5, 2) = '04' AND T1.DEPT_CD = '2' THEN 1 ELSE 0 END) AS SS04,
    ...
    SUM(CASE WHEN SUBSTR(T2.REG_YMD, 5, 2) = '12' AND T1.DEPT_CD = '2' THEN 1 ELSE 0 END) AS SS12,
    COUNT(CASE WHEN T1.DEPT_CD = '2' THEN 1 ELSE NULL END) AS SS_ALL
FROM
	TB_EMP T2
JOIN
	TB_DEPT T1
ON
	T1.COMP_CD = T2.COMP_CD
WHERE
    T1.DEPT_CD IN ('1', '2')
GROUP BY
    SUBSTR(T2.REG_YMD, 1, 4)
ORDER BY
    T2.REG_YMD DESC

 

이런 식으로 조회 가능