DATABASE, MYSQL, GROUP BY

1.

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

SELECT ANIMAL_TYPE, count(ANIMAL_TYPE) from ANIMAL_INS  group by ANIMAL_TYPE

2.

동명 동물 수 찾기

• HAVING 은 GROUP BY의 WHERE절과 같다

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

3.

입양 시각구하기(1)

• DATETIME에서 시간만 뽑고싶을 때는 HOUR을 이용한다

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

4.

입양 시각구하기(2)

• SET 변수를 이용한다

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