CODEWITHSUNDEEP
phpmysql
Travelers
Travelers

Reputation: 67

Get the top selling product's current inventory in mysql

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

enter image description here

if you need more info i will update the question.

Upvotes: 0

Views: 643

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

Assuming that the name of the table is inventory. I use a correlated subquery to get current inventory value

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

Upvotes: 2

Related Questions