[SQL문제풀기] 주문량이 많은 아이스크림들 조회하기

silver

Jul 08, 2025

Contents

문제 MYSQL ORACLE

문제

school.programmers.co.kr

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

MYSQL

내가 작성한 오답

GROUP BY를 사용할 때 SELECT 또는 ORDER BY에서 쓰이는 컬럼들은 GROUP BY에 포함되거나 SUM(), COUNT() 같은 집계함수로 감싸야 한다.


SELECT H.FLAVOR
FROM FIRST_HALF H
JOIN JULY J ON H.FLAVOR = J.FLAVOR
GROUP BY H.FLAVOR 
ORDER BY H.TOTAL_ORDER+J.TOTAL_ORDER DESC
LIMIT 3;

내가 작성한 정답


SELECT H.FLAVOR
FROM FIRST_HALF H
JOIN JULY J ON H.FLAVOR = J.FLAVOR
GROUP BY H.FLAVOR 
ORDER BY sum(H.TOTAL_ORDER)+sum(J.TOTAL_ORDER) DESC
LIMIT 3;

내가 이전에 작성한 정답


SELECT FLAVOR
FROM (SELECT F.FLAVOR , F.TOTAL_ORDER+SUM(J.TOTAL_ORDER) TOTAL
        FROM FIRST_HALF F
        JOIN JULY J ON F.FLAVOR = J.FLAVOR
        GROUP BY J.FLAVOR, F.TOTAL_ORDER
        ORDER BY TOTAL DESC
        LIMIT 3) TB

ORACLE

내가 작성한 정답


--1
SELECT J.FLAVOR
FROM FIRST_HALF H
JOIN JULY J ON H.FLAVOR = J.FLAVOR
GROUP BY J.FLAVOR
ORDER BY SUM(H.TOTAL_ORDER)+SUM(J.TOTAL_ORDER) DESC
FETCH FIRST 3 ROWS ONLY;

--2
SELECT FLAVOR
FROM (SELECT H.FLAVOR, SUM(J.TOTAL_ORDER)+SUM(H.TOTAL_ORDER) O
      FROM FIRST_HALF H
      JOIN JULY J ON H.FLAVOR=J.FLAVOR
      GROUP BY H.FLAVOR
      ORDER BY O DESC) OO
WHERE ROWNUM <=3;