[프로그래머스] GROUP BY (SQL)

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

SELECT ANIMAL_TYPE, count(ANIMAL_TYPE) FROM ANIMAL_INS GROUP BY ANIMAL_TYPE ORDER BY ANIMAL_TYPE;

 

2️⃣ 동명 동물 수 찾기

SELECT NAME, COUNT(NAME) FROM ANIMAL_INS WHERE NAME is NOT NULL GROUP BY NAME HAVING COUNT(NAME) > 1 ORDER BY NAME;

 

3️⃣ 입양 시각 구하기(1)

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

 

4️⃣ 입양 시각 구하기(2)

SET @hour := -1;

SELECT (@hour := @hour + 1) as HOUR, (SELECT COUNT(*) FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = @hour) as COUNT FROM ANIMAL_OUTS WHERE @hour < 23;