Some Context:
I am trying to display all the items with the highest quantity in another table.
I want to display the best-selling items sorted by 'quantity' in the OrderItems table. A productId with a quantity of '5' should come first, then the product quantity of '4'..so on and so forth.
I am only able to retrieve the items that matched the productId, size, and color, but am stuck on how to factor in the quantity.
OrderItems Table
| orderItemsId | orderId | productId | quantity | size | colors |
|---|---|---|---|---|---|
| 1 | 48 | 1 | 1 | small | black |
| 2 | 48 | 2 | 2 | small | black |
| 3 | 48 | 3 | 5 | small | black |
Product Table
| productindex | productId | name |
|---|---|---|
| 1 | 1 | Addidas |
| 2 | 2 | Nike |
| 3 | 3 | NewBalance |
So, the items displayed should be productId 3,2,1
I just need to sum all the productId quantity, regardless of size and color. and is it to retrieve from the products table by ascending order
This is a small code, just to retrieve the products that happen to be in the orderItems table.
SELECT * from Product
inner join OrderItems ON Product.productId = OrderItems.productId where Product.size = OrderItems.size and Product.color = OrderItems.colors
CodePudding user response：
You can order by sum():
SELECT Product.* from Product inner join OrderItems
ON Product.productId = OrderItems.productId AND
Product.size = OrderItems.size and Product.color = OrderItems.colors
GROUP BY Product.productId
ORDER BY sum(OrderItems.quantity) DESC;