ING

안녕하세요 Jin's 입니다.

SQL 고득점 Kit의 GROUP BY 답안 공유드립니다~

모두 MySQL 기준입니다.

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

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

2. 동명 동물 수 찾기

SELECT NAME, COUNT(*)
  FROM ANIMAL_INS
 GROUP BY NAME
HAVING COUNT(NAME) > 1

3. 입양 시각 구하기(1)

SELECT HOUR(DATETIME) AS HOUR
     , COUNT(*)
  FROM ANIMAL_OUTS
 WHERE HOUR(DATETIME)
   AND HOUR(DATETIME) >= '09'
   AND HOUR(DATETIME) <= '19'
 GROUP BY HOUR(DATETIME)
 ORDER BY HOUR(DATETIME)

4. 입양 시각 구하기(2)

SELECT D.HOUR
     , COUNT(H.DATETIME)
  FROM ANIMAL_OUTS H
 RIGHT OUTER JOIN (
                    SELECT LEVEL -1 AS HOUR
                      FROM DUAL
                   CONNECT BY LEVEL <= 24
) D
    ON TO_CHAR(H.DATETIME,'HH24')= D.HOUR
 GROUP BY D.HOUR
 ORDER BY D.HOUR