Getting the most sold product per supplier in sql

Question

Well, I am struggling with this question in SQL using MySql:

I have to give the product that was mostly sold per supplier from the popular open source database called NORTHWIND: https://northwinddatabase.codeplex.com

Now what I wrote is:

SELECT  products.SupplierID ,`order details`.ProductID, count(*) as NumSales FROM `order details` 
    JOIN products ON `order details`.ProductID = products.ProductID
    JOIN orders ON `order details`.OrderID = orders.OrderID
    WHERE `order details`.OrderID 
        IN
        (SELECT OrderID FROM orders 
           WHERE MONTH(OrderDate) = 7 AND YEAR(orderDate) = 1997) 


    group by products.SupplierID  , `order details`.ProductID 
    ORDER BY NumSales desc
    ;

The result is:

that this is all good but I need to give back for example for Supplier 1 Product 1 since it was sold 3 times (at 7/1997)

Adding to the start:

SELECT SupplierID, ProductID, MAX(b.NumSales)
FROM( ... )

gets me closer but it gives my the highest of all suppliers and not for every supplier.

Help will be great.

P.S. This question is similar but the same and didn't completely help me.

I get the feeling this is homework, so just providing an answer isn't useful. I'd use a variation of https://stackoverflow.com/questions/19432913/select-info-from-table-where-row-has-max-date/19433107#19433107 as my approach...use the query you have now to pick out max count and join it back to your data to get the rest of the columns. You will need to determine what logic to use when there is multiple products that sold the maximum (if supplier id 1 had both product id 1 and 2 selling 3, what do you return?) — Twelfth, Mar 26 '18 at 19:24
Yes this is HomeWork, but I don't feel bad asking if it is not asking just for an answer but for the way and also I did (I think) a large part of the way. Thanks, I will look at it and hopefully it will help me! — Tomer, Mar 26 '18 at 19:31
@Twelfth "use the query you have now to pick out max count and join it back to your data to get the rest of the columns" - what do you mean by joing it back? — Tomer, Mar 26 '18 at 19:34
@Icemanind Yes, your right... In a mistake, I left a prior title of a question that I almost asked... Thanks for noticing and notifying me. — Tomer, Mar 26 '18 at 19:36
@Tomer - any query can be a subquery. **select * from (select * from table) a ** is valid. select * from (complicated subquery)a inner join sametablequeriedinsubquery b on a.id = b.id is also valid. — Twelfth, Mar 26 '18 at 19:46
@a_horse_with_no_name your right! I am using MySql. Will edit my question — Tomer, Mar 26 '18 at 20:03
It might be due to the homework exercise, but as a future learning tip, never include spaces in your table names. Use underscores as order_details instead. — Twelfth, Mar 26 '18 at 20:05
@Twelfth Hope I am not bothering you too much. I tried writing a join at the end of the query (also before the group by and also after the order by) now just to doodle around and understand SQL better but I get a "not valid input at this position" could it be because I am using MySql? — Tomer, Mar 26 '18 at 20:10
Yes, your right I saw that this is not a good practice when working on this. Also, your right that I have to still do this b/c of the assignment. Thanks — Tomer, Mar 26 '18 at 20:11

score 1 · Accepted Answer · answered Mar 26 '18 at 20:24

Please know this as a psuedo answer and work with it as you will...appreciate that you are putting in the time to learn this.

select supplier_id, max(num_sales) max_sales
from (put your select statement here)
group by supplier_id

This now gives you what you max num_sales is for each supplier. Something like

supplier_id    max_sales
1              3
2              1
3              2
4              2

Now join this back to your original query to get the product data for the whatever matches to the max.

select a.supplier_id, b.product_id, a.max_sales 
from
(select supplier_id, max(num_sales) max_sales
from (put your select statement here)
group by supplier_id) a
inner join 
(your original query again) b
on a.supplier_id = b.supplier_id
and a.max_sales = b.num_sales

As you learn SQL, you will see that there is usually hundreds of valid working scripts that will give you the answer you want....your job is to find the script that is the quickest to write, the most efficient to run, and meets the criteria of your task. The advantage to the method shown here is it will display multiple records in the event of a tie (supplier_id = 2 has two product that bot have a max sales of one. This query returns both those rows).

just as additional info...other databases allow common table expressions (with clause), however mysql does not. How do you use the "WITH" clause in MySQL? in other databases you are able to simplify this script further.

Thanks Very much! I actually got to a solution thanks to your comments by myself, but I am sure that in your answer I can understand SQL better therefore I will read it tomorrow and probably tag it as an answer — Tomer, Mar 26 '18 at 20:37
If you really feel like going the extra mile...you can use the logic here https://stackoverflow.com/questions/3333665/rank-function-in-mysql to come up with a version of this script that will only return one row per supplier — Twelfth, Mar 26 '18 at 20:48

Getting the most sold product per supplier in sql

1 Answers1