[Programmers] 분기별 분화된 대장균의 개체 수 구하기

문제 링크

정답

-- ECOLI_DATA: ID, PARENT_ID, SIZE_OF_COLONY, DIFFERENTIATION_DATE, GENOTYPE

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

✅ Point

• GROUP BY 에 CONCAT(QUARTER(DIFFERENTIATION_DATE), ‘Q’) 대신 QUARTER(DIFFERENTIATION_DATE) 만 썼을 땐 에러가 발생했음
  • GROUP BY 의 컬럼 값과 SELECT의 컬럼 값이 달라서 그런 듯 (MySQL의 only_full_group_by mode)

    실패 (1055, "Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'programmers.ECOLI_DATA.DIFFERENTIATION_DATE' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by")