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?