[SQL] ORDER BY 안에 GROUP 조건 넣기

주문량이 많은 아이스크림들 조회하기

문제 설명
다음은 아이스크림 가게의 상반기 주문 정보를 담은 FIRST_HALF 테이블과 7월의 아이스크림 주문 정보를 담은 JULY 테이블입니다. FIRST_HALF 테이블 구조는 다음과 같으며, SHIPMENT_ID, FLAVOR, TOTAL_ORDER는 각각 아이스크림 공장에서 아이스크림 가게까지의 출하 번호, 아이스크림 맛, 상반기 아이스크림 총주문량을 나타냅니다. FIRST_HALF 테이블의 기본 키는 FLAVOR입니다. FIRST_HALF테이블의 SHIPMENT_ID는 JULY테이블의 SHIPMENT_ID의 외래 키입니다.

NAME	TYPE	NULLABLE
SHIPMENT_ID	INT(N)	FALSE
FLAVOR	VARCHAR(N)	FALSE
TOTAL_ORDER	INT(N)	FALSE

JULY 테이블 구조는 다음과 같으며, SHIPMENT_ID, FLAVOR, TOTAL_ORDER 은 각각 아이스크림 공장에서 아이스크림 가게까지의 출하 번호, 아이스크림 맛, 7월 아이스크림 총주문량을 나타냅니다. JULY 테이블의 기본 키는 SHIPMENT_ID입니다. JULY테이블의 FLAVOR는 FIRST_HALF 테이블의 FLAVOR의 외래 키입니다. 7월에는 아이스크림 주문량이 많아 같은 아이스크림에 대하여 서로 다른 두 공장에서 아이스크림 가게로 출하를 진행하는 경우가 있습니다. 이 경우 같은 맛의 아이스크림이라도 다른 출하 번호를 갖게 됩니다.

NAME	TYPE	NULLABLE
SHIPMENT_ID	INT(N)	FALSE
FLAVOR	VARCHAR(N)	FALSE
TOTAL_ORDER	INT(N)	FALSE

문제
7월 아이스크림 총 주문량과 상반기의 아이스크림 총 주문량을 더한 값이 큰 순서대로 상위 3개의 맛을 조회하는 SQL 문을 작성해주세요.

정답 풀이

SELECT f.FLAVOR
FROM FIRST_HALF f JOIN JULY j
ON f.FLAVOR	 = j.FLAVOR
GROUP BY j.FLAVOR
ORDER BY (SUM(j.TOTAL_ORDER)  + f.TOTAL_ORDER) DESC
LIMIT 3

문제부터 이해가 참 쉽지 않아서 어떻게 풀어야 할지도 몰랐던 것 같다.

실행 중에도 첫 줄 SELECT 에 보여지는 정보가 너무 없어서이기도 하고.

ORDER BY (SUM(j.TOTAL_ORDER) + f.TOTAL_ORDER) DESC
이 줄을 내가 혼자서 생각해내지 못했다는 점인데

j.FLAVOR 기준으로 그룹바이 하고, 그룹한 j.TOTAL_ORDER 의 합들을
f.TOTAL_ORDER 로 더한다.

SELECT f.FLAVOR, f.TOTAL_ORDER, SUM(j.TOTAL_ORDER)
FROM FIRST_HALF f JOIN JULY j
ON f.FLAVOR = j.FLAVOR
GROUP BY j.FLAVOR
ORDER BY (f.TOTAL_ORDER + SUM(j.TOTAL_ORDER)) DESC
LIMIT 3

이렇게 본다면

FLAVOR	TOTAL_ORDER	SUM(j.TOTAL_ORDER)
strawberry	3100	740
chocolate	3200	520
white_chocolate	3100	350

조금 더 편하게 알 수 있었다.

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

문제 설명
다음은 어느 자동차 대여 회사의 자동차 대여 기록 정보를 담은 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블입니다. CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블은 아래와 같은 구조로 되어있으며, HISTORY_ID, CAR_ID, START_DATE, END_DATE 는 각각 자동차 대여 기록 ID, 자동차 ID, 대여 시작일, 대여 종료일을 나타냅니다.

Column name	Type	Nullable
HISTORY_ID	INTEGER	FALSE
CAR_ID	INTEGER	FALSE
START_DATE	DATE	FALSE
END_DATE	DATE	FALSE

문제
CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 대여 시작일을 기준으로 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차들에 대해서 해당 기간 동안의 월별 자동차 ID 별 총 대여 횟수(컬럼명: RECORDS) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 월을 기준으로 오름차순 정렬하고, 월이 같다면 자동차 ID를 기준으로 내림차순 정렬해주세요. 특정 월의 총 대여 횟수가 0인 경우에는 결과에서 제외해주세요.

내 코드

SELECT MONTH(START_DATE) MONTH, CAR_ID, COUNT(*) RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE "2022-08-01" <= START_DATE <= "2022-10-31"
GROUP BY 1, 2
HAVING RECORDS >= 1
ORDER BY 1, 2 DESC

코드 실행 결과

MONTH	CAR_ID	RECORDS
8	29	4
8	28	3
8	27	5
8	26	1
8	25	5
8	23	3

이런 식으로 월별로 길게 나오긴 하는데 선행 조건을 빼먹었다.

SELECT sub.MONTH, sub.CAR_ID, sub.MONTH_COUNT
FROM (SELECT CAR_ID, MONTH(START_DATE) MONTH, COUNT(*) MONTH_COUNT
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE "2022-08-01" <= START_DATE <= "2022-10-31"
    GROUP BY CAR_ID, MONTH
    HAVING MONTH_COUNT >=1) sub
GROUP BY 1,2
WHERE SUM(sub.MONTH_COUNT) >= 5
ORDER BY 1,2 DESC

코드 결과 : SQL 실행 중 오류가 발생하였습니다.
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE SUM(sub.MONTH_COUNT) >= 5
ORDER BY 1,2 DESC' at line 8

머리로는 알겠는데 코드로 구현을 못하니까 너무 답답함.
시간이 더 지체되기는 곤란해서 정답을 참고했다.

정답 코드

SELECT Month(start_date) MONTH,
       car_id,
       Count(*)          RECORDS
FROM   car_rental_company_rental_history
WHERE  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)
       AND start_date BETWEEN '2022-08-01' AND '2022-10-31'
GROUP  BY month,
          car_id
ORDER  BY 1,
          2 DESC

WHERE car_id IN(~~~) 이부분에서 또 배우고 간다.
나도 해결방법의 접근을 이렇게 했는데도 IN 을 사용할 줄 몰라서 해결하지 못했다.
함수들의 기본적인 사용법 부터 다시 익혀야 되겠습니다...