프로그래머스 SQL 고득점 Kit - GROUP BY 풀이(MYSQL/ORACLE)

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

https://programmers.co.kr/learn/courses/30/lessons/59040

코딩테스트 연습 - 고양이와 개는 몇 마리 있을까

-- MYSQL
SELECT animal_type, count(animal_id) as count
from animal_ins
group by animal_type
order by name desc

-- ORACLE
SELECT animal_type, count(animal_id) as count 
from animal_ins
group by animal_type 
order by animal_type

 

2. 동명 동물 수 찾기

https://programmers.co.kr/learn/courses/30/lessons/59041

코딩테스트 연습 - 동명 동물 수 찾기

-- MYSQL
SELECT NAME, COUNT(NAME)
FROM ANIMAL_INS
GROUP BY NAME
HAVING COUNT(NAME) >= 2
ORDER BY NAME

-- ORACLE
SELECT NAME, COUNT(NAME)
FROM ANIMAL_INS
GROUP BY NAME
HAVING COUNT(NAME) >= 2
ORDER BY NAME

 

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

https://programmers.co.kr/learn/courses/30/lessons/59412

코딩테스트 연습 - 입양 시각 구하기(1)

-- MYSQL
SELECT HOUR(DATETIME) AS HOUR, COUNT(DATETIME) AS COUNT
FROM ANIMAL_OUTS
WHERE HOUR(DATETIME) >= 9 AND HOUR(DATETIME) <= 19
GROUP BY HOUR 
ORDER BY HOUR

-- ORACLE
SELECT TO_CHAR(DATETIME,'HH24') AS HOUR, COUNT(*) AS COUNT
FROM ANIMAL_OUTS
WHERE TO_CHAR(DATETIME,'HH24') BETWEEN 09 AND 19
GROUP BY TO_CHAR(DATETIME,'HH24')
ORDER BY TO_CHAR(DATETIME,'HH24')

 

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

https://programmers.co.kr/learn/courses/30/lessons/59413

코딩테스트 연습 - 입양 시각 구하기(2)

-- MYSQL
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

-- ORACLE
SELECT HOUR, COUNT(O.DATETIME) AS COUNT
FROM
(
    SELECT LEVEL-1 AS HOUR
    FROM DUAL 
    CONNECT BY LEVEL<=24
)A LEFT join ANIMAL_OUTS O
ON A.HOUR = to_char(O.DATETIME,'HH24')
GROUP BY HOUR
ORDER BY HOUR;