[SQL] 프로그래머스 SQL 풀이 - SUM, MAX, MIN

조건에 맞는 아이템들의 가격의 총합 구하기 

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

프로그래머스

 

SELECT SUM(PRICE) AS TOTAL_PRICE
FROM ITEM_INFO 
WHERE RARITY = 'LEGEND'

 

물고기 종류 별 대어 찾기

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

프로그래머스

 

SELECT ID
, t2.FISH_NAME 
, LENGTH
FROM FISH_INFO t1 
JOIN FISH_NAME_INFO t2 
ON t1.FISH_TYPE = t2.FISH_TYPE 
WHERE (t1.FISH_TYPE, LENGTH) IN
    (SELECT FISH_TYPE, MAX(LENGTH) AS LENGTH
    FROM FISH_INFO
    GROUP BY FISH_TYPE)
ORDER BY t1.ID


--- 다른 풀이 : HAVING 절 사용 
SELECT t1.ID, t2.FISH_NAME, t1.LENGTH
FROM FISH_INFO t1
JOIN FISH_NAME_INFO t2
    ON t1.FISH_TYPE = t2.FISH_TYPE
WHERE t1.FISH_TYPE IN 
(
    SELECT FISH_TYPE
    FROM FISH_INFO 
    GROUP BY FISH_TYPE
    HAVING LENGTH = MAX(LENGTH)
) ORDER BY ID

 

연도별 대장균 크기의 편차 구하기 

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

프로그래머스