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

https://programmers.co.kr/learn/courses/30/parts/17044

프로그래머스

 

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

SELECT ANIMAL_TYPE, COUNT(*)
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE;

 

동명 동물 수 찾기

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

/*
SELECT - 5순위(필수)
FROM - 1순위(필수)
WHERE - 2순위
GROUP BY - 3순위
HAVING - 4순위
ORDER BY - 6순위
*/

 

입양 시각 구하기(1)

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

 

입양 시각 구하기(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
ORDER BY @hour;