GROUP BY, FROM절 SUB쿼리

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

프로그래머스

 

 

MYSQL의 경우 FROM절에 서브쿼리를 쓸 경우 테이블 명을 붙여줘야 한다.

SELECT COUNT(*) AS FISH_COUNT, MAX(LENGTH) AS MAX_LENGTH, FISH_TYPE
FROM (SELECT ID, FISH_TYPE, IFNULL(LENGTH,10) AS LENGTH, TIME FROM FISH_INFO)sub
GROUP BY FISH_TYPE
HAVING AVG(LENGTH) >= 33
ORDER BY FISH_TYPE

 

 

또는 AS로

SELECT COUNT(*) AS FISH_COUNT, MAX(LENGTH) AS MAX_LENGTH, FISH_TYPE
FROM (SELECT ID, FISH_TYPE, IFNULL(LENGTH,10) AS LENGTH, TIME FROM FISH_INFO) AS SUB_TABLE
GROUP BY FISH_TYPE
HAVING AVG(LENGTH) >= 33
ORDER BY FISH_TYPE