[프로그래머스 - ORACLE] 5월 식품들의 총매출 조회하기

---

CREATE 문

CREATE TABLE FOOD_PRODUCT  (
PRODUCT_ID			VARCHAR(10)	NOT NULL,
PRODUCT_NAME			VARCHAR(50)	NOT NULL,
PRODUCT_CD			VARCHAR(10)	NULL,
CATEGORY			VARCHAR(10)	NULL,
PRICE				NUMBER		NULL
)


CREATE TABLE FOOD_ORDER (
ORDER_ID			VARCHAR(10)	NOT NULL,
PRODUCT_ID			VARCHAR(5)	NOT NULL,
AMOUNT				NUMBER		NOT NULL,
PRODUCE_DATE			DATE		NULL,
IN_DATE				DATE		NULL,
OUT_DATE			DATE		NULL,
FACTORY_ID			VARCHAR(10)	NOT NULL,
WAREHOUSE_ID			VARCHAR(10)	NOT NULL
)

 

 

---

• 내 풀이

SELECT
    P.PRODUCT_ID,
    P.PRODUCT_NAME,
    SUM(P.PRICE * O.AMOUNT) AS TOTAL_SALES
FROM FOOD_PRODUCT P INNER JOIN FOOD_ORDER O
ON P.PRODUCT_ID = O.PRODUCT_ID
AND TO_CHAR(O.PRODUCE_DATE, 'YYYY-MM') = '2022-05'
GROUP BY P.PRODUCT_ID , P.PRODUCT_NAME
ORDER BY TOTAL_SALES DESC , P.PRODUCT_ID ASC;