[SQL]5월 식품들의 총매출 조회하기(프로그래머스)☆☆☆☆

 

코딩테스트 연습 - 5월 식품들의 총매출 조회하기 | 프로그래머스 스쿨 (programmers.co.kr)

프로그래머스

 

 

grouping 안해서 틀림 

SELECT p.product_id, product_name, sum(price) as 'total_sales'
from food_product p, food_order o
where (produce_date between '2022-05-01' and '2022-05-31' )
and p.product_id=o.product_id 
order by total_sales desc, p.product_id;

 

 

2. 틀림....

-- 코드를 입력하세요
SELECT p.product_id, product_name, sum(price) as 'total_sales'
from food_product p, food_order o
where (produce_date between '2022-05-01' and '2022-05-31' )
and p.product_id=o.product_id 
group by p.product_id
order by total_sales desc, p.product_id;

 

 

1. 정답... 어렵다.

SELECT FP.PRODUCT_ID, PRODUCT_NAME, TOTAL_SALES*PRICE AS 'TOTAL_SALES'
FROM FOOD_PRODUCT FP

JOIN
(SELECT PRODUCT_ID, SUM(AMOUNT) AS 'TOTAL_SALES'
FROM FOOD_ORDER 
WHERE PRODUCE_DATE BETWEEN '2022-05-01' AND '2022-05-31'
GROUP BY PRODUCT_ID) AS FO
ON FO.PRODUCT_ID=FP.PRODUCT_ID

ORDER BY TOTAL_SALES DESC, PRODUCT_ID;

 

2. 정답 2

select product_id, product_name , sum*price as total_sales
from food_product join
	(select product_id, sum(amount) as sum
	from food_order 
	where date_format(produce_date,'%Y-%m-%d') like  "2022-05-%"
	group by product_id) as fo
using(product_id)
order by total_sales desc, product_id;