[Programmers] 특정 조건을 만족하는 물고기별 수와 최대 길이 구하기 - 298519

HAVING 절에 AVG ( CASE WHEN ~ END ) >= 33 가 가능하다!

여기서 10 이하나 NULL 값을 10으로 대체해주는 코드가 될 줄이야..

 

Question

 

FISH_INFO에서 평균 길이가 33cm 이상인 물고기들을 종류별로 분류하여 잡은 수, 최대 길이, 물고기의 종류를 출력하는 SQL문을 작성해주세요. 결과는 물고기 종류에 대해 오름차순으로 정렬해주시고, 10cm이하의 물고기들은 10cm로 취급하여 평균 길이를 구해주세요.

컬럼명은 물고기의 종류 'FISH_TYPE', 잡은 수 'FISH_COUNT', 최대 길이 'MAX_LENGTH'로 해주세요.

 

 

Point

 

• HAVING AVG ( CASE WHEN ~ END ) >= 33

 

Code (After 25.06.)

 

SELECT COUNT(*) AS FISH_COUNT
        , MAX(LENGTH) AS MAX_LENGTH
        , FISH_TYPE

FROM FISH_INFO
GROUP BY 3
  HAVING AVG(IFNULL(LENGTH, 10)) >= 33

ORDER BY 3

 

Code (Before)

 

SELECT COUNT(*)    AS FISH_COUNT, 
       MAX(LENGTH) AS MAX_LENGTH, 
       FISH_TYPE
    FROM FISH_INFO
    GROUP BY FISH_TYPE
      HAVING AVG(CASE
                    WHEN LENGTH > 10 THEN LENGTH
                    ELSE 10
                 END) >= 33
    ORDER BY FISH_TYPE

 

now me

On my github