I've been Googling for a few hours... thought this would be easy, but clearly not for me :)
I've got sales data in two tables and I want to generate a weekly sales report for a specific item. For this purpose, I don't care about dollar values, just number of units. An a "week" is either a calendar week (whatever start day, I don't care) or just 7-day chunks back from current (so week 1 is the last 7 days, week 2 is 8 - 15 days ago, etc) - whichever is easier. I'm simply trying to monitor sales trends over time. Preferably it would span back over years so that if its the first week of January, for example, it wouldn't show just one record.
The data comes from ZenCart. The relevant tables/column structure is here:
Table "orders" has columns: orders_id, date_purchased
Table "orders_products" has columns: orders_id, products_id, product_quantity
Where I'm having trouble is with the joins and syntax.
This worked for my needs:
SELECT o.date_purchased, CONCAT(YEAR(o.date_purchased), LPAD(WEEK(o.date_purchased), 2, '0')) as weekyear, op.products_id, SUM( op.products_quantity )
FROM orders_products op
LEFT JOIN orders o ON op.orders_id = o.orders_id
WHERE op.products_id = 331
GROUP BY weekyear
ORDER BY weekyear