분기별 분화된 대장균의 개체 수 구하기 - MySQL

SELECT A.QUARTER, COUNT(*) ECOLI_COUNT
FROM
(
SELECT CASE WHEN MONTH(DIFFERENTIATION_DATE) IN ('1','2','3') THEN '1Q'
    WHEN MONTH(DIFFERENTIATION_DATE) IN ('4','5','6') THEN '2Q'
    WHEN MONTH(DIFFERENTIATION_DATE) IN ('7','8','9') THEN '3Q'
    ELSE '4Q'
    END AS QUARTER
FROM ECOLI_DATA
) A
GROUP BY A.QUARTER
ORDER BY A.QUARTER

 

* Quarter 사용

SELECT CONCAT(QUARTER(DIFFERENTIATION_DATE),'Q') QUARTER, COUNT(*) ECOLI_COUNT
FROM ECOLI_DATA
GROUP BY QUARTER
ORDER BY QUARTER