프로그래머스 SQL - GROUP BY

1

진료과별 총 예약 횟수 출력하기

SELECT MCDP_CD AS 진료과코드, COUNT(APNT_YMD) AS 5월예약건수
FROM APPOINTMENT
WHERE APNT_YMD LIKE '2022-05%'
GROUP BY MCDP_CD
ORDER BY 2,1

2

식분분류별 가장 비싼 식품의 정보 조회하기

SELECT CATEGORY, PRICE AS MAX_PRICE, PRODUCT_NAME
FROM FOOD_PRODUCT
WHERE PRICE IN (
    SELECT MAX(PRICE)
    FROM FOOD_PRODUCT
    GROUP BY CATEGORY
    HAVING
        CATEGORY = '식용유' OR CATEGORY = '과자' OR CATEGORY = '국' OR CATEGORY = '김치'
    ) 
    AND (CATEGORY = '식용유' OR CATEGORY = '과자' OR CATEGORY = '국' OR CATEGORY = '김치')
ORDER BY PRICE DESC

3

성분으로 구분한 아이스크림 총 주문량

SELECT INGREDIENT_TYPE, SUM(TOTAL_ORDER) AS TOTAL_ORDER
FROM FIRST_HALF
JOIN ICECREAM_INFO
ON FIRST_HALF.FLAVOR = ICECREAM_INFO.FLAVOR
GROUP BY INGREDIENT_TYPE

4

즐겨찾기가 가장 많은 식당 정보 출력하기

SELECT A.FOOD_TYPE, A.REST_ID, A.REST_NAME, A.FAVORITES
FROM REST_INFO A JOIN (
    SELECT FOOD_TYPE, MAX(FAVORITES) AS FAVORITES
    FROM REST_INFO
    GROUP BY FOOD_TYPE
) B -- B: 각 카테고리별 최대 FAVORITES
ON A.FAVORITES = B.FAVORITES AND A.FOOD_TYPE = B.FOOD_TYPE 
-- B에 해당하는 컬럼만 남기기 (교집합)
ORDER BY FOOD_TYPE DESC

5

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

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

6

동명 동물 수 찾기

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

7

입양 시각 구하기(1)

SELECT DATE_FORMAT(DATETIME, '%H') AS HOUR, COUNT(*) AS COUNT
FROM ANIMAL_OUTS
GROUP BY HOUR
HAVING HOUR >= 9 AND HOUR <= 19
ORDER BY HOUR

8

년, 월, 성별 별 상품 구매 회원 수 구하기

SELECT YEAR(SALES_DATE)AS YEAR
       ,MONTH(SALES_DATE)AS MONTH
       ,GENDER
       ,COUNT(DISTINCT(ONLINE.USER_ID))AS USERS
FROM ONLINE_SALE AS ONLINE
LEFT JOIN USER_INFO AS USER
ON ONLINE.USER_ID= USER.USER_ID
WHERE GENDER IS NOT NULL
GROUP BY YEAR,MONTH,GENDER
ORDER BY YEAR,MONTH,GENDER

9

입양 시각 구하기(2)

WITH RECURSIVE TIME AS (
    SELECT 0 AS HOUR
    UNION ALL
    SELECT HOUR+1 FROM TIME WHERE HOUR < 23 )

SELECT A.HOUR
     , CASE WHEN B.COUNT IS NULL THEN 0 ELSE B.COUNT END AS COUNT
  FROM TIME A
  LEFT JOIN (
    SELECT DATE_FORMAT(DATETIME, '%H') AS HOUR
         , COUNT(DISTINCT ANIMAL_ID) AS COUNT
      FROM ANIMAL_OUTS
     GROUP BY DATE_FORMAT(DATETIME, '%H') ) B
    ON A.HOUR = B.HOUR
 ORDER BY A.HOUR ;

10

가격대 별 상품 개수 구하기

SELECT CASE WHEN 0 <= PRICE AND PRICE < 10000 THEN 0
            WHEN 10000 <= PRICE AND PRICE < 20000 THEN 10000
            WHEN 20000 <= PRICE AND PRICE < 30000 THEN 20000
            WHEN 30000 <= PRICE AND PRICE < 40000 THEN 30000
            WHEN 40000 <= PRICE AND PRICE < 50000 THEN 40000
            WHEN 50000 <= PRICE AND PRICE < 60000 THEN 50000
            WHEN 60000 <= PRICE AND PRICE < 70000 THEN 60000
            WHEN 70000 <= PRICE AND PRICE < 80000 THEN 70000
            WHEN 80000 <= PRICE AND PRICE < 90000 THEN 80000
            END AS PRICE_GROUP, COUNT(*)
FROM PRODUCT
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP