[SQL 고득점 Kit] GROUP BY

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

SELECT ANIMAL_TYPE, COUNT(*) AS count 
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE 
order by animal_type

 

📍 [동명 동물 수 찾기]

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

 

📍 [입양 시각 구하기(1)]

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

 

📍 [입양 시각 구하기(2)]

SET @hour := 0;

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