[프로그래머스] GROUP BY (MySQL)

성분으로 구분한 아이스크림 총 주문량(Lv.2)

https://school.programmers.co.kr/learn/courses/30/lessons/133026

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

 

 

 

가격대 별 상품 개수 구하기(Lv.2)

https://school.programmers.co.kr/learn/courses/30/lessons/131530

SELECT TRUNCATE(PRICE, -4) AS PRICE_GROUP, COUNT(*) AS PRODUCTS
FROM PRODUCT
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP

내림 TRUNCATE

 

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

https://school.programmers.co.kr/learn/courses/30/lessons/132202

프로그래머스

SELECT MCDP_CD AS 진료과코드, COUNT(*) AS 5월예약건수
FROM APPOINTMENT
WHERE DATE_FORMAT(APNT_YMD, '%Y-%m-%d') >= '2022-05-01' AND DATE_FORMAT(APNT_YMD, '%Y-%m-%d') <= '2022-05-31'
GROUP BY MCDP_CD
ORDER BY 5월예약건수, 진료과코드

 

 

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

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

프로그래머스

SELECT ANIMAL_TYPE, COUNT(*) ANIMAL_TYPE
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY FIELD(ANIMAL_TYPE, 'Cat', 'Dog')

정렬 필드 우선순위 FIELD(COL, 'COL1', 'COL2')

 

동명 동물 수 찾기

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

SELECT NAME, COUNT(*) AS CNT
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
GROUP BY NAME
HAVING COUNT(*) > 1
ORDER BY NAME

 

 

 

입양 시각 구하기(1) (Lv.2)

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

프로그래머스

SELECT HOUR(DATETIME) AS HOUR, COUNT(DATETIME) AS COUNTS
FROM ANIMAL_OUTS
GROUP BY HOUR
HAVING HOUR >= 9 AND HOUR < 20
ORDER BY HOUR

YEAR, HOUR, MINUTE, SECOND, ADDDATE(DATE, inverval), SUBDATE(DATE, interval)

 

 

자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/151137

SELECT CAR_TYPE, COUNT(*) AS CARS
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS LIKE '%시트%'
GROUP BY CAR_TYPE
ORDER BY CAR_TYPE

 

대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/151139

SELECT MONTH(START_DATE) AS MONTH, CAR_ID, COUNT(*) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE CAR_ID IN (
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE MONTH(START_DATE) BETWEEN 8 AND 11
    GROUP BY CAR_ID
    HAVING COUNT(*) >= 5
    ) AND MONTH(START_DATE) BETWEEN 8 AND 11
GROUP BY MONTH, CAR_ID
HAVING RECORDS > 0
ORDER BY MONTH, CAR_ID DESC

 

 

 

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

https://school.programmers.co.kr/learn/courses/30/lessons/131123#

나의 풀이

WHERE 절에서 FOOD_TYPE 별 MAX(FAVORITES) 인 REST_ID 와 일치하는 식당으로 만들게 했는데 오답으로 처리된다..

SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM REST_INFO
WHERE REST_ID IN (
    SELECT REST_ID
    FROM REST_INFO
    GROUP BY FOOD_TYPE
    HAVING MAX(FAVORITES)
)
ORDER BY FOOD_TYPE DESC;

 

정답

SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM REST_INFO
WHERE (FOOD_TYPE, FAVORITES) IN (
    SELECT FOOD_TYPE, MAX(FAVORITES)
    FROM REST_INFO
    GROUP BY FOOD_TYPE
)
ORDER BY FOOD_TYPE DESC;

 

 

카테고리 별 도서 판매량 집계하기

 

https://school.programmers.co.kr/learn/courses/30/lessons/144855

SELECT CATEGORY, SUM(SALES) AS TOTAL_SALES
FROM BOOK A
JOIN (
    SELECT BOOK_ID, SUM(SALES) AS SALES
    FROM BOOK_SALES
    WHERE MONTH(SALES_DATE) = 1
    GROUP BY BOOK_ID
) B ON A.BOOK_ID=B.BOOK_ID
GROUP BY CATEGORY
ORDER BY CATEGORY