[SQL][프로그래머스] 상품 별 오프라인 매출 구하기 - INNER JOIN + GROUP BY + SUM MYSQL ORACLE

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

프로그래머스

코드설명

INNER JOIN + GROUP BY + SUM 을 활용합니다.

 

먼저 INNER JOIN을 통해 각 데이터들을 모두 묶어준뒤 ( 판매가 * 갯수 ) 로 매출을 계산합니다.

코드

-- 코드를 입력하세요
SELECT PRODUCT_CODE , ( PRODUCT.PRICE * SUM(SALES_AMOUNT) ) AS SALES
FROM PRODUCT
INNER JOIN OFFLINE_SALE
ON PRODUCT.PRODUCT_ID = OFFLINE_SALE.PRODUCT_ID
GROUP BY PRODUCT_CODE
ORDER BY SALES DESC, PRODUCT_CODE ASC;

 

SELECT P.PRODUCT_CODE AS PRODUCT_CODE, P.PRICE * SUM(OS.SALES_AMOUNT) AS SALES
FROM PRODUCT AS P
INNER JOIN OFFLINE_SALE AS OS
ON P.PRODUCT_ID = OS.PRODUCT_ID
GROUP BY PRODUCT_CODE
ORDER BY SALES DESC, PRODUCT_CODE ASC

 

ORACLE

SELECT 절에는 GROUP BY 에 선언된 SELECT문만 사용가능합니다.

SELECT P.PRODUCT_CODE AS PRODUCT_CODE, P.PRICE * SUM(OS.SALES_AMOUNT) AS SALES
FROM PRODUCT P
INNER JOIN OFFLINE_SALE OS
ON P.PRODUCT_ID = OS.PRODUCT_ID
GROUP BY P.PRODUCT_CODE, P.PRICE
ORDER BY SALES DESC, PRODUCT_CODE ASC