SQL 고득점 Kit - GROUP BY (3)

GROUP BY

출제 빈도 보통, 평균 점수 보통, 문제 세트 4개

MySQL 기준으로 풀이하였습니다.

(1) 고양이와 개는 몇 마리 있을까 (Level 2)

SELECT ANIMAL_TYPE, COUNT(ANIMAL_TYPE) as count
FROM ANIMAL_INS 
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE

(2) 동명 동물 수 찾기 (Level 2)

SELECT NAME, COUNT
FROM (
    SELECT NAME, COUNT(NAME) as COUNT
    FROM ANIMAL_INS
    GROUP BY NAME
    ORDER BY NAME
    ) name_group
WHERE COUNT >= 2

(3) 입양 시각 구하기 (1) (Level 2)

SELECT HOUR(DATETIME), COUNT(HOUR(DATETIME))
FROM ANIMAL_OUTS 
WHERE HOUR(DATETIME) >= 9
AND HOUR(DATETIME) <= 19 
GROUP BY HOUR(DATETIME)
ORDER BY HOUR(DATETIME)

(4) 입양 시각 구하기 (2) (Level 4)

SELECT H.HOUR, IFNULL(OUTS.COUNT, 0) AS COUNT
FROM (
    SELECT 0 AS HOUR
    UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 
    UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION SELECT 16
    UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION SELECT 20 UNION SELECT 21 UNION SELECT 22 UNION SELECT 23) H
LEFT JOIN (
        SELECT HOUR(DATETIME) AS HOUR, COUNT(HOUR(DATETIME)) AS COUNT
        FROM ANIMAL_OUTS
        GROUP BY HOUR) OUTS ON OUTS.HOUR = H.HOUR

 

4번이 갑자기 난이도가 어려워져서 검색 후 참조했습니다.

앞으로도 틀 만들어야 하면 SELECT 해와서 UNION 해야겠어요.