0

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?

2
Contributors
2
Replies
3
Views
4 Years
Discussion Span
Last Post by eman neercs
0

Use a LEFT JOIN. You can then in your SELECT check for NULL values (missing SKU's) and return 0 (if that is what you want).

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.