Code:
SELECT products.name, COUNT(orders.orderID) AS numberOfOrders FROM products LEFT JOIN orders ON products.sku = orders.SKUordered WHERE products.manufacturer = 'somecompany' GROUP BY orders.SKUordered;
That should do what you'd like. You could also add an order by clause if you want to get the most ordered items first.
That'll be number of orders an item appears on. Assuming you have a quantity field in the orders table, you could also do sum(quantity) in place of the count to get the most sold items.
Be aware that this will be very CPU intensive if you've got a large number of records. It took well over 1 minute on our machines to do a similar query, but hopefully you won't have as much data to try to join together.
Brian.
Need help creating a MySQL query
Linear Mode
