Here is my
INNER JOIN amz_event_fees ON amz_event_shipment_items.id = amz_event_fees.shipment_item_id
INNER JOIN amz_shipment_events ON amz_shipment_events.id = amz_event_shipment_items.shipment_event_id
WHERE amz_event_fees.currency = 'USD'
AND amz_shipment_events.event_type <> 'RefundEvent'
AND amz_shipment_events.posted_date BETWEEN '2016-5-1 07:00:00' AND '2016-5-7 06:59:59'
GROUP BY amz_event_shipment_items.seller_sku
Many shipment_events for each date
Each shipment_event HAS MANY shipment_item / BELONGS TO ONE event
Each shipment_item HAS MANY shipment_fee / BELONGS TO ONE item
You are multiplying the quantities by the number of fees. Use an
EXISTS clause when looking for mere existence.
select sum(i.quantity), i.seller_sku from amz_event_shipment_items i where exists ( select * from amz_event_fees f where f.currency = 'USD' and f.shipment_item_id = i.id ) and exists ( select * from amz_shipment_events e where e.event_type <> 'RefundEvent' and e.posted_date between '2016-05-01 07:00:00' and '2016-05-07 06:59:59' and e.id = i.shipment_event_id ) group by i.seller_sku;
(MySQL is known to be slow on
IN clauses sometimes, so I am using
EXISTS here, although I like