[코딩테스트 연습- SQL] GROUP BY

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

SELECT ANIMAL_TYPE, count(ANIMAL_ID) as count from ANIMAL_INS group by ANIMAL_TYPE order by ANIMAL_TYPE;

2. 동명 동물 수 찾기

SELECT NAME, count(NAME) as COUNT FROM ANIMAL_INS group by NAME
having NAME is not null and count(NAME) >=2 order by NAME;

3. 입양 시각 구하기(1)

SELECT HOUR(DATETIME) AS HOUR, COUNT(HOUR(DATETIME)) AS COUNT
FROM ANIMAL_OUTS
GROUP BY HOUR
HAVING HOUR BETWEEN 9 AND 19
-- HAVING HOUR >= 9 AND HOUR <= 19 
ORDER BY HOUR

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

WITH RECURSIVE NUM AS (
    SELECT 0 AS HOUR
    UNION ALL
    SELECT HOUR+1 FROM NUM WHERE HOUR<23
)

SELECT A.HOUR, IFNULL(B.COUNT, 0) AS COUNT
FROM NUM A
LEFT JOIN (
    SELECT HOUR(DATETIME) AS HOUR, COUNT(ANIMAL_ID) AS COUNT
    FROM ANIMAL_OUTS
    GROUP BY 1
) AS B ON A.HOUR=B.HOUR