I have a table which shows our top sellers and Weeks of Stock left. Its generated by joining an order db with a stock db. I'm struggling with try to capture the really fast movers.
Here is the script
SELECT
a.EAN,
a.SKU,
a.Name,
Sum(a.OrderedQuantity)
Round(Avg(a.Price),2) as AvgPrice
b.Quantity,
round((b.Quantity/Sum(a.OrderedQuantity)),0) as WOS
FROM orders a
inner join stock b on a.sku = b.sku
Where a.condit = "New"
And a.EAN <> ""
and b.sku
and WeekofYear(OrderDate) between ' . $strtwk . ' and ' . $endwk . '
Group by EAN
Limit 0, 50
The issue is that if there is no stock left (we've sold out in that week) then the innerjoin statement fails as zero value sku's are removed from the stock db.
I was thinking of running 2 queries (with and without matching the stock.sku and then seeing the exceptions i.e. top sellers with no stock.
Is there a more effective way of finding the top 50 both with and without stock?