[MySQL] 프로그래머스_GROUP BY

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

음식종류별로 즐겨찾기 수가 가장 많은 식당의 음식종류, id, 식당이름, 즐겨찾기 수를 조회. 음식 종류 기준으로 내림차순

SELECT food_type, rest_id, rest_name, favorites FROM rest_info A
    WHERE favorites = (SELECT MAX(favorites) FROM rest_info B WHERE A.food_type = B.food_type)
    GROUP BY food_type
    ORDER BY food_type DESC

food_type으로 그룹을 나누면 하나의 행만 있으므로 food_type외에 맞는 값을 넣어줘야함. food_type이 나왔을 때, 이 food_type과 같은 food_type을 갖고 있는 애들 중 favorites이 max값인 것들을 출력하도록함. 헷갈림.

 

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

2022년 8월부터 2022년 10월까지 총 대여횟수가 5회 이상인 자동자들에 대해서 해당 기간 동안의 월별 자동차id 별 총 대여 횟수를 records로 출력. 결과는 월을 기준으로 오름차순, 아이디로 내림

SELECT MONTH(start_date) AS month, car_id, COUNT(*) AS records FROM car_rental_company_rental_history
    WHERE (start_date BETWEEN '2022-08-01' AND '2022-10-31') AND car_id IN (
        SELECT car_id FROM car_rental_company_rental_history B
        WHERE B.start_date BETWEEN '2022-08-01' AND '2022-10-31'
        GROUP BY car_id
        HAVING COUNT(*) >= 5
    )
    GROUP BY month, car_id
    HAVING records > 0
    ORDER BY month, car_id DESC

MONTH를 이용하면 01이렇게가 아니라 1 이렇게 뽑아낼 수 있음. month와 car_id를 기준으로 그룹을 나눠야함.

근데 car_id는 대여 횟수가 5번이 넘어야하므로 WHERE절 안에서 다시 한 번 필터링함. 

 

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

2022년 1월의 카테고리 별 도서 판매량 합산해서, 카테고리와 총판매량 리스트 출력. 카테고리명 기준으로 오름차순

SELECT category, SUM(s.sales) AS total_sales FROM book_sales S
    JOIN book B ON b.book_id = s.book_id
    WHERE sales_date LIKE '2022-01%'
    GROUP BY category
    ORDER BY category

book_id가 공통이므로 이를 기준으로 JOIN함. 이때, category를 기준으로 그룹으로 나누느데, 이때 sales에는 각 카테고리별로 여러 개의 값이 있을거임. 그래서 SUM을 이용해 합해줌

 

자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기

2022년 10월 16일에 대해서 각각의 자동차 id별로 대여 가능, 대여중 여부를 판단. id를 기준으로 내림차순

SELECT car_id, IF(SUM(IF(start_date <= '2022-10-16' AND end_date >= '2022-10-16',1,0)) > 0,'대여중','대여 가능') AS availability FROM car_rental_company_rental_history
    GROUP BY car_id
    ORDER BY car_id DESC

10월 16일에 대여가 가능하다는 것은 10월 16일이 start_date와 end_date사이에 없어야함. 그래서 그런 경우가 있다면 1을 반환하고 이를 더해서 0보다 크면 대여중이라는 것.

 

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

'통풍시트', '열선시트','가죽시트' 중 하나 이상의 옵션 포함된 자동차가 자동차 종류별로 몇 대인지 출력. 자동차 종류를 기준으로 오름차순

SELECT car_type, COUNT(*) AS cars FROM car_rental_company_car
    WHERE options LIKE '%통풍시트%' or options LIKE '%열선시트%' or options LIKE '%가죽시트%'
    GROUP BY car_type
    ORDER BY car_type

%가 빈 칸이어도 되는 듯.

REGEXP를 이용해서 '통풍시트|열선시트|가죽시트' 이렇게 작성해도 된다고함.

 

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

2022년 5월에 진료예약한 사람들 몇명인지 진료과코드 별로 출력. 컬럼명은 '진료과코드', '5월예약건수'

SELECT mcdp_cd AS '진료과 코드', COUNT(*) AS '5월예약건수' FROM appointment
    WHERE apnt_ymd LIKE '2022-05%'
    GROUP BY mcdp_cd
    ORDER BY COUNT(*), mcdp_cd

'진료과코드'로 컬럼명을 정했으면 ORDER BY도 가능한줄알았음. 

아무튼 LIKE로 apnt_ymd를 필터링하고 진료과목으로 그룹을 나눠서 해결

 

조건에 맞는 사용자와 총 거래금액 조회하기

두 개의 테이블에서 완료된 중고 거래 총 금액이 70만원 이상인 사람의 회원ID, 닉네임, 총거래금액을 조회. 총 거래금액 기준으로 오름차순

SELECT user_id, nickname, SUM(price) AS total_sales FROM used_goods_board b
    JOIN used_goods_user u ON u.user_id = b.writer_id
    WHERE b.status = 'done'
    GROUP BY user_id
    HAVING SUM(price) >= 700000
    ORDER BY SUM(price)

겹치는 것은 writerid와 userid이므로 이를 기준으로 두 개의 테이블 JOIN

이때, board테이블의 status가 done인 것들만 필터링.

user_id로 그룹을 나눈 뒤 SUM을 이용해 전체 합 구해서 해결

 

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

식품분류별로 가격이 제일 비싼 식품의 분류, 가격, 이름 조회. 식품분류는 과자, 국, 김치, 식용유인 경우만 출력. 가격을 기준으로 내림차순

SELECT a.category, MAX(price) AS max_price, (
    SELECT product_name FROM food_product B
        WHERE a.category = b.category
        ORDER BY price DESC
        LIMIT 1
) FROM food_product A
    WHERE category IN ('과자','국','김치','식용유')
    GROUP BY category
    ORDER BY price DESC

product_name을 어떻게 뽑아야하나 생각했었는데, 카테고리가 같은 것들을 가격을 기준으로 정렬한 뒤 LIMIT을 이용해 맨 위의 값을 출력하면됨.

 

저자 별 카테고리 별 매출액 집계하기

2022년 1월의 도서 판매 데이터를 기준으로 저자 별, 카테고리 별 매출액 출력. 저자 id오름차순, 같다면 카테고리 내림차순

SELECT a.author_id, a.author_name, category, SUM(s.sales*b.price) AS total_sales
    FROM book_sales s, book b, author a
    WHERE sales_date LIKE '2022-01%' AND b.book_id = s.book_id AND a.author_id = b.author_id
    GROUP BY a.author_id, a.author_name, category
    ORDER BY author_id, category DESC

JOIN을 안쓰고 FROM으로 여러 개를 불러와도되는지 몰랐었음.

 

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

각 아이스크림 성분 타입과 성분 타입에 대한 아이스크림의 총주문량을 총 주문량이 작은 순서대로 조회.

SELECT ingredient_type, SUM(total_order) AS total_order FROM icecream_info i
    JOIN first_half f ON f.flavor = i.flavor
    GROUP BY ingredient_type
    ORDER BY total_order

 

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

고양이와 개가 각각 몇마리인지 조회. 고양이를 먼저 조회

SELECT animal_type, COUNT(*) AS count FROM animal_ins
    WHERE animal_type IN ('cat','dog')
    GROUP BY animal_type
    ORDER BY animal_type

고양이를 먼저 조회해야하므로 오름차순 정렬. c가 d보다 앞에있기때문

 

동명 동물 수 찾기

이름이 두 번 이상 쓰인 이름과 쓰인 횟수 조회. 이름없는애들 제외, 이름순 정렬

SELECT name, COUNT(*) AS COUNT FROM animal_ins
    WHERE name != 'null'
    GROUP BY name
    HAVING COUNT(*) >= 2
    ORDER BY name

 

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

년,월,성별 별로 상품을 구매한 회원수를 조회. 년,월,성별 기준으로 오름차순. 성별정보 없으면 제외

SELECT YEAR(o.sales_date) AS year, MONTH(o.sales_date) AS month, gender, COUNT(DISTINCT u.user_id) AS users FROM user_info u
    JOIN online_sale o ON u.user_id = o.user_id
    WHERE gender IS NOT NULL
    GROUP BY year, month, gender
    ORDER BY year, month, gender

NULL인 것을 판단할때 != NULL 이렇게 했는데 이러면 안됨. IS NOT NULL을 사용하거나 IN을 이용해 조건에 맞는 것만 조회할 수 있도록 해야함.

 

입양 시각 구하기(2)

0시부터 23시까지 시간대별로 입양이 몇 건이나 발생했는지 조회. 시간대 순으로 정렬

WITH RECURSIVE temp AS (
    SELECT 0 as hour
    UNION ALL
    SELECT hour + 1 FROM temp WHERE hour < 23
)
SELECT hour, 0 AS count FROM temp
    WHERE hour NOT IN (
        SELECT HOUR(datetime) FROM animal_outs
            GROUP BY HOUR(datetime)
    )
UNION
SELECT HOUR(datetime) AS hour, COUNT(*) AS count FROM animal_outs
    GROUP BY HOUR(datetime)
    ORDER BY hour

WITH RECURSIVE 구문을 처음 알았음. 반복문과 비슷한 느낌으로 테이블을 생성할 수 있음. 

temp라는 이름의 테이블을 생성하는데, 0부터 23까지 hour값을 갖는 테이블이 만들어짐. 이 중에서 animal_out에 없는 것들만 필터링해서 컬럼 수를 맞춰준 뒤 UNION하고 정렬해서 해결

 

가격대 별 상품 개수 구하기

만원 단위 가격대별로 상품 개수 출력

SELECT floor(price / 10000) * 10000 as price_group, count(*) FROM product
    GROUP BY price_group
    ORDER BY price_group

만원 단위 맞추는게 헷갈렸음. price를 10000으로 나눈 것을 내림하면 만원단위를 구할 수 있음. 이를 기준으로 그룹화하여 해결

 

입양 시각 구하기(1)

9시부터 19시까지 시간대별로 입양 건 수 조회. 시간대순으로 정렬

SELECT HOUR(datetime) as hour, count(*) as count FROM animal_outs
    GROUP BY hour
    HAVING hour >= 9 AND hour <= 19
    ORDER BY hour