0

I’m pretty new to SQL and I want to find the top selling product from each supplier, I tried it with a Max and SUM functions as you can see below but it doesn't quite work like I imagined. Can someone help me solve this?

I got 4 tables which I need to join:

  • Supplier with ID, SName
  • Order with ID, Supplier_ID
  • Order_Position with ID, Order_ID, Product_ID, quantity
  • Products with ID, PName
SELECT X.ID, X.SNAME
FROM (SELECT S.ID, P.PNAME, SUM(OP.QUANTITY) AS SUM
      FROM ORDER_POSITION AS OP
      INNER JOIN ORDER AS O ON O.ID = OP.ORDER_ID
      INNER JOIN SUPPLIER AS S ON S.ID = O.SUPPLIER_ID
      INNER JOIN PRODUCT AS P ON P.ID = OP.PRODUCT_ID
      GROUP BY S.ID, P.PNAME) AS X
WHERE X.SUM = (SELECT (MAX(OP.QUANTITY) AS MAXSUM
                       FROM ORDER_POSITION AS OP)
tripleee
  • 175,061
  • 34
  • 275
  • 318
forg
  • 1
  • 1
    Sample data and expected results would help immensely – Charlieface Aug 04 '22 at 11:34
  • Unless you’re only trying to get the one with the biggest SUM (which is what the query implies), seems a [top 1 per group](https://stackoverflow.com/q/6841605/61305) problem. – Aaron Bertrand Aug 04 '22 at 11:35
  • I agree that this appears to be a top 1 per group problem, so I've closed it as a dupe of said question. If the other question doesn't answer the problem, then please [edit] your question to explain why, and add some sample data (in a consumable format) and expected results. If you make the [edit] feel free to ping me in the comments afterwards and I'll be happy to reopen the question should it successfully demonstrate it isn't a dupe. – Thom A Aug 04 '22 at 11:45

0 Answers0