[프로그래머스] SQL 고득점 Kit : GROUP BY - 오라클 정답

 

프로그래머스

 

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

SELECT ANIMAL_TYPE
     , COUNT(ANIMAL_TYPE) AS count
FROM   ANIMAL_INS
GROUP  BY ANIMAL_TYPE
ORDER  BY ANIMAL_TYPE

 

동명 동물 수 찾기

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

 

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

SELECT hour(datetime) as HOUR, COUNT(*)
FROM   ANIMAL_OUTS
WHERE  hour(datetime) BETWEEN 9 AND 19
GROUP  BY hour(datetime)
ORDER  BY hour(datetime)

 

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

SELECT A.DT, COUNT(*)
FROM  (SELECT TO_CHAR(DATETIME, 'HH24') AS DT
       FROM   ANIMAL_OUTS) A
GROUP  BY A.DT HAVING A.DT BETWEEN 9 AND 19
ORDER  BY A.DT

 

 

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

WITH HC AS(
    SELECT LEVEL-1 AS HOUR, 0 AS COUNT
    FROM   DUAL
    CONNECT  BY LEVEL <= 24
), HC2 AS(
    SELECT HOUR
         , COUNT(*) AS COUNT
    FROM  (SELECT TO_CHAR(DATETIME, 'HH24') AS HOUR
           FROM   ANIMAL_OUTS)
    GROUP  BY HOUR
    ORDER  BY HOUR
)

SELECT A.HOUR, NVL(B.COUNT, 0) AS COUNT
FROM   HC A, HC2 B
WHERE  A.HOUR = B.HOUR(+)
ORDER  BY A.HOUR

0시 ~ 23시를 담고있지만 COUNT가 0인 테이블과 조회결과를 가진 테이블을 WITH 구문으로 생성해준 뒤, OUTER JOIN을 통해 답을 구할 수 있습니다.