Select 3 products (product name and order number) sold in the most quantity in one order (in MySQL)-CodePudding

Whole db with all tables is in the LINK

I tried using this code:

SELECT Products.ProductName, OrderDetails.OrderID, SUM(Quantity) FROM Products
INNER JOIN OrderDetails ON Products.ProductID = OrderDetails.ProductID
GROUP BY ProductName
ORDER BY SUM(Quantity) DESC

But the result was wrong for sure...

CodePudding user response：

You were almost there, and you need to group by two columns orderdetails.orderid and products.productid

SELECT products.productname,
       products.productid,
       orderdetails.orderid,
       Sum(quantity) AS MaxQuantity
FROM   products
       INNER JOIN orderdetails
               ON products.productid = orderdetails.productid
GROUP  BY orderdetails.orderid,
          products.productid
ORDER  BY maxquantity DESC LIMIT 0, 3