프로그래머스 SQL GROUP BY

고양이와 개는 몇 마리 있을까

# (1)
SELECT
    ANIMAL_TYPE as TYPE, COUNT(ANIMAL_TYPE) as COUNT
FROM
    ANIMAL_INS
WHERE
    ANIMAL_TYPE IN ('Dog', 'Cat')
GROUP BY ANIMAL_TYPE

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

동명 동물 수 찾기

SELECT t1.NAME, t1.COUNT FROM
    (SELECT NAME, COUNT(NAME) as COUNT
    FROM ANIMAL_INS
    WHERE NAME IS NOT NULL
    GROUP BY NAME) as t1
WHERE t1.COUNT >= 2
ORDER BY t1.NAME

입양 시각 구하기

SELECT HOUR(DATETIME) as DT_HOUR, COUNT(DATETIME) as DT_COUNT 
FROM ANIMAL_OUTS 
WHERE HOUR(DATETIME) >= 9 AND HOUR(DATETIME) < 20 
GROUP BY DT_HOUR
ORDER BY DT_HOUR

입양 시간 구하기(2)

SET @h = -1;
SELECT
    @h:=@h+1 AS HOUR,
    (SELECT COUNT(1) FROM ANIMAL_OUTS WHERE @h = HOUR(datetime)) as COUNT
FROM ANIMAL_OUTS
WHERE @h < 23