SELECT ANIMAL_TYPE, COUNT(*) as count
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE
SELECT NAME, COUNT(NAME)
FROM ANIMAL_INS
GROUP BY NAME -- 이름별로 묶음
HAVING COUNT(NAME)>=2 -- 2번 이상 쓰인 이름 / GROUP BY로 묶여진 것을 세기 위해 HAVING 사용
ORDER BY NAME; -- 결과는 이름 순으로 조회
SELECT HOUR(DATETIME) AS HOUR, COUNT(*)
FROM ANIMAL_OUTS
GROUP BY HOUR -- 시간으로 묶음
HAVING HOUR BETWEEN 9 AND 20 -- 09~19:59 사이
ORDER BY HOUR(DATETIME); -- 오름차순
- 입양 시각 구하기(2)
- 원하는 값
- 첫번째 시도
SELECT HOUR(DATETIME) AS HOUR, COUNT(1) AS COUNT
FROM ANIMAL_OUTS
GROUP BY 1
HAVING 1 BETWEEN 0 AND 23
ORDER BY 1;
WITH RECURSIVE 테이블명 AS(
SELECT 초기값 AS 별명1
UNION ALL
SELECT 별명1 계산식 FROM 테이블명 WHERE 제어문)
-- 사용예제
WITH RECURSIVE CTE AS(
SELECT 1 AS H
UNION ALL
SELECT H+1 FROM CTE WHERE H<5)
REF : https://hyunmin1906.tistory.com/149
WITH RECURSIVE time AS (
SELECT 0 AS h
UNION ALL
SELECT h+1 FROM TIME WHERE h < 23
)
SELECT h AS HOUR, count(DATETIME)
FROM time
LEFT JOIN animal_outs on h = hour(datetime)
GROUP BY h
ORDER BY h;