[SQL][프로그래머스] 물고기 종류 별 잡은 수 구하기 - INNER JOIN + GROUP BY MYSQL

https://school.programmers.co.kr/learn/courses/30/lessons/293257

프로그래머스

코드설명

INNER JOIN + GROUP BY 를 활용합니다.

 

 

INNER JOIN으로 FISH_INFO와 FISH_NAME_INFO를 타입으로 교차집합을 묶어서 가져옵니다.

이후에, FISH_NAME으로 GROUP BY 시킨뒤 값 행별로 갯수와 이름을 출력합니다.

 

유의해야할점은, FISH_NAME으로 GROUP BY  를 진행해줘야만 SELECT문에서 GROUP BY 가 올바르게 처리됩니다.

 

 

문제의 답을 찾아가는 과정입니다.

먼저, 집계조건으로 FISH_TYPE을 설정하고, INNER JOIN 합니다.

SELECT FNI.*, FI.*
FROM FISH_NAME_INFO AS FNI
INNER JOIN FISH_INFO AS FI
ON FNI.FISH_TYPE = FI.FISH_TYPE

위와 같은 데이터가 JOIN됩니다.

이제, 위 데이터들을 FISH_TYPE, FISH_NAME으로 묶어도 가능하지만,

FISH_NAME으로만 묶으면 더 간단해집니다.

SELECT COUNT(*) AS FISH_COUNT, FNI.FISH_NAME
FROM FISH_NAME_INFO AS FNI
INNER JOIN FISH_INFO AS FI
ON FNI.FISH_TYPE = FI.FISH_TYPE
GROUP BY FNI.FISH_NAME
ORDER BY FISH_COUNT DESC;

 

항상 기억해야할점은, GROUP BY 절을 사용한다면 SELECT절에는 집계함수나, GROUP BY 절에 포함된 필드 및 컬럼만 출력할 수 있습니다. (RDBMS에서 불필요한 데이터는 가져오지 않기 떄문이겠습니다.)

코드

SELECT COUNT(*) AS FISH_COUNT, FISH_NAME_INFO.FISH_NAME
FROM FISH_INFO 
INNER JOIN FISH_NAME_INFO
ON FISH_INFO.FISH_TYPE = FISH_NAME_INFO.FISH_TYPE
GROUP BY FISH_NAME_INFO.FISH_NAME
ORDER BY FISH_COUNT DESC;

 

SELECT COUNT(*) AS FISH_COUNT, FNI1.FISH_NAME AS FISH_NAME
FROM FISH_INFO AS FI1
INNER JOIN FISH_NAME_INFO AS FNI1
ON FI1.FISH_TYPE = FNI1.FISH_TYPE
GROUP BY FNI1.FISH_NAME
ORDER BY FISH_COUNT DESC;

 

 

만약 아래와 같이 작성하면 어떻게 될까요? 오류가 납니다.

SELECT COUNT(*) AS COUNT, FNI.FISH_NAME
FROM FISH_NAME_INFO AS FNI
INNER JOIN FISH_INFO AS FI
ON FNI.FISH_TYPE = FI.FISH_TYPE
GROUP BY FNI.FISH_TYPE
ORDER BY FISH_COUNT DESC;

SELECT 절에서 FNI.FISH_NAME을 선택했지만, GROUP BY 절에서는 FNI.FISH_TYPE으로 그룹화하고 있습니다. SQL의 규칙상, 집계 함수(COUNT)를 제외한 SELECT 절의 모든 컬럼은 GROUP BY 절에 포함되어야 합니다.

그러므로, 아래와 같이 수정합니다. GROUP BY절에 FNI.FISH_NAME으로 이중 그룹화를 시키는 것 입니다.

만약, GROUP BY 로 FISH_TYPE, FISH_NAME을 사용할 경우입니다(조금 느립니다)

SELECT COUNT(*) AS FISH_COUNT, FNI.FISH_NAME
FROM FISH_NAME_INFO AS FNI
INNER JOIN FISH_INFO AS FI
ON FNI.FISH_TYPE = FI.FISH_TYPE
GROUP BY FNI.FISH_TYPE, FNI.FISH_NAME
ORDER BY FISH_COUNT DESC;