![[SQL문제풀기] 온라인 쇼핑몰의 월 별 매출액 집계](https://image.inblog.dev?url=https%3A%2F%2Fsource.inblog.dev%2Ffeatured_image%2F2025-03-11T08%3A03%3A10.939Z-bb05dd64-1b6d-408a-9d90-7c1a7fdf4780&w=3840&q=75)
문제
SQLite
내가 작성한 정답
select *, ordered_amount+canceled_amount total_amount
from (select strftime('%Y-%m',order_date) order_month ,
sum(case when quantity>0 then quantity*price end) ordered_amount,
sum(case when quantity<0 then quantity*price end) canceled_amount
from orders o
join order_items i on o.order_id = i.order_id
group by order_month)
order by order_month내가 작성한 정답2
Sign는 주어진 숫자의 부호를 반환하는 함수로
양수일 경우 1
0일 경우 0
음수일 경우 -1를 반환한다.
select *, ordered_amount+canceled_amount total_amount
from (select strftime('%Y-%m',order_date) order_month ,
sum(case sign(quantity) when 1 then quantity*price end) ordered_amount,
sum(case sign(quantity) when -1 then quantity*price end) canceled_amount
from orders o
join order_items i on o.order_id = i.order_id
group by order_month)
order by order_monthShare article