获取产品最近3个月销售的数量

Question

I have 3 mysql tables looks like this :

• product(product_id, name)
• order(order_id, order_date)
• order_detail(order_detail_id, product_id, order_id, quantity)

I want to get the sum of the quantities of product sold in the last 3 months and grouping them by the product like this :

product_id  | quantity_sold_last_3_mouth    
 1          | 50    
 2          | 156    
 3          | 78

The problem is how to join only the rows of the table order_detail that are creating the last threes month since the date is set only in the table order

Thanks a lot for any idea or help

Answer 1

Depending on how you define "last 3 months":

select 
    od.product_id
  , sum(od.quantity) as quantity_sold_last_3_month
from order o
  inner join order_detail od
    on o.order_id = od.order_id
where 
  o.order_date >= last_day(now()) + interval 1 day - interval 3 month #first of month, e.g. Jan 1 from today March 25
  #od.order_date >= now()-interval 3 month #exactly 3 months back, e.g. Dec 25 from today
group by od.product_id