[Group By 21~24] SQL 코딩테스트 고득점 Kit

21. 노선별 평균 역 사이 거리 조회하기 (LV 2)
22. 물고기 종류 별 잡은 수 구하기 (LV 2)
23. 월별 잡은 물고기 수 구하기 (LV 2)
24. 특정 조건을 만족하는 물고기별 수와 최대 길이 구하기 (LV 3)

21. 노선별 평균 역 사이 거리 조회하기 (LV 2)

정답:

SELECT ROUTE
    ,  CONCAT(ROUND(SUM(D_BETWEEN_DIST), 1), 'km') AS TOTAL_DISTANCE
    ,  CONCAT(ROUND(AVG(D_BETWEEN_DIST), 2), 'km') AS AVERAGE_DISTANCE
  FROM SUBWAY_DISTANCE
 GROUP
    BY ROUTE
 ORDER
    BY SUM(D_BETWEEN_DIST) DESC

22. 물고기 종류 별 잡은 수 구하기 (LV 2)

정답:

SELECT COUNT(i.ID) AS FISH_COUNT
    ,  n.FISH_NAME
  FROM FISH_INFO AS i
LEFT
  JOIN FISH_NAME_INFO AS n ON i.FISH_TYPE = n.FISH_TYPE
 GROUP
    BY n.FISH_NAME
 ORDER
    BY FISH_COUNT DESC

23. 월별 잡은 물고기 수 구하기 (LV 2)

정답:

SELECT COUNT(ID) AS FISH_COUNT
    ,  MONTH(TIME) AS MONTH
  FROM FISH_INFO
 GROUP
    BY MONTH(TIME)
HAVING COUNT(ID) IS NOT NULL
 ORDER
    BY MONTH 

24. 특정 조건을 만족하는 물고기별 수와 최대 길이 구하기 (LV 3)

정답:

WITH BASE AS(
    SELECT FISH_TYPE
        ,  AVG(CASE WHEN LENGTH IS NOT NULL THEN LENGTH 
                    ELSE 10 END) AS AVG_LENGTH
        ,  MAX(CASE WHEN LENGTH IS NOT NULL THEN LENGTH 
                    ELSE 10 END) AS MAX_LENGTH
        ,  COUNT(ID) AS FISH_COUNT
      FROM FISH_INFO
     GROUP
        BY FISH_TYPE
)
SELECT FISH_COUNT
    ,  MAX_LENGTH
    ,  FISH_TYPE
  FROM BASE
 WHERE AVG_LENGTH >= 33
 ORDER
    BY FISH_TYPE