php - Get the top selling product's current inventory in mysql

Question

I want to get the top selling product's current inventory from the database. I have successfully fetch the top-selling products but unable to fetch their current inventory in the same query.

SELECT product_name, product_code, SUM(qty) As TotalQuantity FROM `order_items` GROUP BY product_code ORDER BY SUM(qty) DESC LIMIT 5

inventory table is something like that

if you need more info i will update the question.

标签： phpmysql

Answer 1

假设表的名称是inventory。我使用相关子查询来获取当前库存值

SELECT oi.product_name, oi.product_code,
SUM(oi.qty) AS TotalQuantity,
(SELECT SUM(inv.qty) FROM `inventory` AS inv WHERE inv.product_code = oi.product_code) AS CurrentStock
FROM `order_items` AS oi
GROUP BY oi.product_code
ORDER BY TotalQuantity DESC
LIMIT 5