[MYSQL] 5월 식품들의 총매출 구하기

사용 언어 - MYSQL

프로그래머스 LV 4) 5월 식품들의 총매출 구하기

프로그래머스

 

 

정답

JOIN (정답 맞춘 여부 X) 

FOOD_ORDER 테이블을 기준으로 JOIN 한다.

A.PRODUCE_DATE LIKE '2022-05%'

A.PRODUCT_ID를 기준으로 그루핑 해주고, B.PRICE를 A.AMOUNT에 곱해주면 총매출을 구할 수 있다.

SELECT P.PRODUCT_ID, PRODUCT_NAME, SUM(PRICE*AMOUNT) AS TOTAL_SALES
FROM FOOD_PRODUCT AS P
JOIN FOOD_ORDER AS O
ON P.PRODUCT_ID = O.PRODUCT_ID
WHERE PRODUCE_DATE LIKE '2022-05%'
GROUP BY P.PRODUCT_ID
ORDER BY TOTAL_SALES DESC, P.PRODUCT_ID;

 같은 뜻의 코드!

SELECT A.PRODUCT_ID, B.PRODUCT_NAME, SUM(B.PRICE * A.AMOUNT) AS TOTAL_SALES
FROM FOOD_ORDER AS A
JOIN (SELECT * FROM FOOD_PRODUCT) AS B
ON A.PRODUCT_ID = B.PRODUCT_ID
WHERE A.PRODUCE_DATE LIKE '2022-05%'
GROUP BY A.PRODUCT_ID
ORDER BY TOTAL_SALES DESC, B.PRODUCT_ID;

 

 처음에 시도했던 방식 - 내오답풀이

너무 복잡하게 생각한듯...

-- 오답
SELECT P.PRODUCT_ID, P.PRODUCT_NAME,
(P.PRICE * O.AMOUNT) AS TOTAL_SALES

FROM FOOD_PRODUCT AS P
JOIN (SELECT PRODUCT_ID, PRODUCE_DATE, SUM(AMOUNT) AS AMOUNT
      FROM FOOD_ORDER
      GROUP BY PRODUCT_ID
      HAVING YEAR(PRODUCE_DATE) = 2022 AND MONTH(PRODUCE_DATE) = 5
     ) AS O
ON P.PRODUCT_ID = O.PRODUCT_ID

ORDER BY TOTAL_SALES DESC, P.PRODUCT_ID;

 

 

 

레퍼런스

• 정답 깃허브

https://github.com/yyeongeun/codingtest/blob/main/%ED%94%84%EB%A1%9C%EA%B7%B8%EB%9E%98%EB%A8%B8%EC%8A%A4%20MYSQL/5%EC%9B%94%EC%8B%9D%ED%92%88%EB%93%A4%EC%9D%98%EC%B4%9D%EB%A7%A4%EC%B6%9C%EC%A1%B0%ED%9A%8C%ED%95%98%EA%B8%B0.sqld