[SQL Kit] GROUP BY

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

My Code

-- 음식종류별로 즐겨찾기수가 가장 많은 식당
-- 음식 종류, ID, 식당 이름, 즐겨찾기수 조회
-- 음식 종류 기준 DESC

# SELECT FOOD_TYPE, REST_ID, REST_NAME, MAX(FAVORITES) AS FAVORITES
# FROM REST_INFO
# GROUP BY FOOD_TYPE 
# ORDER BY FOOD_TYPE DESC
# ;
# 틀린 이유 => FOOD_TYPE으로 그룹화할 경우, 각 FOOD_TYPE 그룹 내에 여러 개의 REST_ID와 REST_NAME이 존재할 수 있다.

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

---

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

My Code

-- 2022년 10월 16일에 
-- 대여 중인 자동차인 경우 '대여중' 이라고 표시 / 대여 중이지 않은 자동차인 경우 '대여 가능'을 표시하는 컬럼 AVAILABILITY 추가
-- 자동차 ID와 AVAILABILITY 리스트 출력
-- 2022년 10월 16일인 경우에도 '대여중'으로 표시
-- 자동차 ID 기준 DESC
SELECT CAR_ID, IF(CAR_ID IN (
            SELECT CAR_ID 
            FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
            WHERE START_DATE <= '2022-10-16' AND '2022-10-16' <= END_DATE
        ), '대여중', '대여 가능') AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC
;

Extra Code

SELECT CAR_ID, ( CASE
                WHEN CAR_ID IN (
                SELECT CAR_ID 
                FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
                WHERE '2022-10-16' BETWEEN START_DATE AND END_DATE
                ) THEN '대여중' 
                    ELSE '대여 가능'
                    END
                    ) AS AVAILABILITY

---

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

My Code

-- 완료된 중고 거래의 총금액이 70만 원 이상인 사람
-- 회원 ID, 닉네임, 총거래금액 조회
-- 총거래금액 기준 ASC
SELECT U.USER_ID, U.NICKNAME, SUM(B.PRICE) AS TOTAL_SALES
FROM USED_GOODS_USER AS U JOIN USED_GOODS_BOARD AS B
    ON U.USER_ID = B.WRITER_ID
# WHERE B.PRICE >= 700000 AND B.STATUS = 'DONE'
WHERE B.STATUS = 'DONE'
GROUP BY B.WRITER_ID
HAVING TOTAL_SALES >= 700000
ORDER BY TOTAL_SALES ASC
;

---

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

My Code

-- 2022년 1월의 도서 판매 데이터를 기준
-- 저자 별, 카테고리 별 매출액(TOTAL_SALES = 판매량 * 판매가) 
-- 저자 ID(AUTHOR_ID), 저자명(AUTHOR_NAME), 카테고리(CATEGORY), 매출액(SALES) 출력
-- 저자 ID 기준 ASC, 카테고리 기준 DESC
SELECT B.AUTHOR_ID, A.AUTHOR_NAME, B.CATEGORY, SUM(B.PRICE * S.SALES) AS TOTAL_SALES
FROM BOOK AS B
    JOIN AUTHOR AS A ON B.AUTHOR_ID = A.AUTHOR_ID
    JOIN BOOK_SALES AS S ON B.BOOK_ID = S.BOOK_ID
WHERE S.SALES_DATE LIKE '2022-01-%'
GROUP BY B.AUTHOR_ID, B.CATEGORY
ORDER BY B.AUTHOR_ID ASC, B.CATEGORY DESC
;

---

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

My Code

-- '통풍시트', '열선시트', '가죽시트' 중 하나 이상의 옵션이 포함
-- 자동차 종류 별로 몇 대인지 출력
-- 자동차 수에 대한 컬럼명은 CARS
-- 자동차 종류 기준 ASC
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 ASC
;

---

만약에,, 두 개 이상의 옵션이 포함된 자동차였다면?

SELECT CAR_TYPE, COUNT(*) AS CARS
FROM (
    SELECT CAR_TYPE,
           (CASE WHEN OPTIONS LIKE '%통풍시트%' THEN 1 ELSE 0 END
          + CASE WHEN OPTIONS LIKE '%열선시트%' THEN 1 ELSE 0 END
          + CASE WHEN OPTIONS LIKE '%가죽시트%' THEN 1 ELSE 0 END) AS OPTION_COUNT
    FROM CAR_RENTAL_COMPANY_CAR
) AS OPTIONED_CARS
WHERE OPTION_COUNT >= 2
GROUP BY CAR_TYPE
ORDER BY CAR_TYPE ASC;

---

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

My Code

-- 대여 시작일을 기준으로 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차들
-- 월별 자동차 ID 별 총 대여 횟수(컬럼명: RECORDS) 
-- 월 기준 ASC => 자동차 ID 기준 DESC
-- 특정 월의 총 대여 횟수가 0인 경우 결과에서 제외
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
        WHERE 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 ASC, CAR_ID DESC
;

---

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

My Code

-- 2022년 5월에 예약한 환자 수
-- 진료과코드 별로 조회
-- 컬럼명은 '진료과 코드', '5월예약건수'로 지정
-- 진료과별 예약한 환자 수를 기준으로 ASC => 진료과 코드를 기준으로 ASC
SELECT MCDP_CD AS '진료과 코드', COUNT(*) AS '5월예약건수'
FROM APPOINTMENT 
WHERE APNT_YMD LIKE '2022-05-%'
GROUP BY MCDP_CD
ORDER BY `5월예약건수` ASC, `진료과 코드` ASC
;

Point

• ORDER BY 5월예약건수 ASC, 진료과 코드 ASC

  백틱(`)을 써야한다!!!
  아니면 그대로 쓰는것도 방법 : ORDER BY COUNT(*) ASC, MCDP_CD ASC

---

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

My Code

-- 2022년 1월의 카테고리 별 도서 판매량을 합산하고
-- 카테고리(CATEGORY), 총 판매량(TOTAL_SALES) 리스트를 출력
-- 카테고리명을 기준으로 오름차순
SELECT CATEGORY, SUM(S.SALES) AS TOTAL_SALES
FROM BOOK AS B JOIN (
    SELECT *
    FROM BOOK_SALES
    WHERE SALES_DATE LIKE '2022-01-%'
    ) AS S
    ON B.BOOK_ID = S.BOOK_ID
GROUP BY CATEGORY
ORDER BY CATEGORY ASC
;