[프로그래머스] MYSQL.GROUP BY

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

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

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

SELECT ANIMAL_TYPE, COUNT(ANIMAL_TYPE)
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE;

 

2. 동명 동물 수 찾기

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

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

SELECT NAME, COUNT(NAME) AS 'COUNT' 
FROM ANIMAL_INS 
GROUP BY NAME 
HAVING COUNT(NAME) > 1 
ORDER BY NAME

 

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

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

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

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

SELECT DATE_FORMAT(DATETIME, "%H") as HOUR, 
    COUNT(DATE_FORMAT(DATETIME, "%H")) as COUNT 
    FROM ANIMAL_OUTS 
        GROUP BY HOUR
        HAVING HOUR >= 9 
           AND HOUR < 20              
    ORDER BY HOUR ASC;

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

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

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

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;

SET @hour := -1;
# 변수선언 0으로 맞추기위해 
SELECT (@hour := @hour + 1) as HOUR,
# 반복하면서 식별자안에 0~23을 할당
(SELECT COUNT(*) FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = @hour) as COUNT
FROM ANIMAL_OUTS
WHERE @hour < 23;