[프로그래머스] SQL 고득점 Kit 문제 - GROUP BY

 

해당 페이지는 프로그래머스의 SQL 문제에 대한 제 풀이 방법을 정리한 내용입니다.

---

 

[문제1] 고양이와 개의 마리 수 구하기

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

 

 

 

[문제2] 동명 동물 수 찾기

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

 

 

 

[문제3] 입양 시각 구하기1

SELECT EXTRACT(HOUR FROM CAST(DATETIME AS TIMESTAMP)) AS "HOUR", COUNT(ANIMAL_ID) AS "COUNT" 
	FROM ANIMAL_OUTS
	GROUP BY EXTRACT(HOUR FROM CAST(DATETIME AS TIMESTAMP))
	HAVING EXTRACT(HOUR FROM CAST(DATETIME AS TIMESTAMP)) BETWEEN 9 AND 19	
	ORDER BY  HOUR;

 

 

 

 

[문제4] 입양 시각 구하기2

#임시테이블 생성
WITH DATA AS(SELECT SUBSTR(TO_CHAR(DATETIME, 'YYYYMMDDHH24MISS'),9,2) AS "HOUR" 
             FROM ANIMAL_OUTS),
    
    TIME AS (SELECT LEVEL-1 AS "HOUR" FROM DUAL
            CONNECT BY LEVEL <= 24)
    
#메인쿼리
SELECT TIME.HOUR AS "HOUR", COUNT(DATA.HOUR) AS "COUNT" 
	FROM DATA RIGHT OUTER JOIN TIME
	ON DATA.HOUR = TIME.HOUR
	GROUP BY TIME.HOUR
	ORDER BY TIME.HOUR;