CODEWITHSUNDEEP
sqlsql-servert-sqlgroup-by
Chris
Chris

Reputation: 41

SQL Server need to find suppliers who supply the most different products

I have two tables I need information from, Products and Suppliers. Both these tables have a SupplierID column I am trying to use to join them together to retrieve the right info.

The output I need is SupplierID and ContactName from the Suppliers table. The correct output should contain only two suppliers, so I attempted something like this, but ran into a conversion error converting nvarchar value to a data type int. I am not supposed to count how many products they supply but aggregate functions seem like the best method to me. 

SELECT TOP 2 ContactName, COUNT(Products.SupplierID) AS Supply
FROM Products 
LEFT JOIN Suppliers ON Suppliers.ContactName = Products.SupplierID
GROUP BY Products.SupplierID, Suppliers.ContactName
ORDER BY Supply;

I have tried many different queries but none will work. I am confused on how to join these tables without running into errors. All the products have a unique ProductID as well. The correct output should look something like this:

   7     John Smith
  12     John Sample

Upvotes: 0

Views: 898

Answers (2)

GMB
GMB

Reputation: 222582

Both these tables have a SupplierID column I am trying to use to join them together to retrieve the right info

If so, you should be joining on that column accross tables.

Also, it is a good practice to use table aliases and prefix each column with the table it belongs to.

Another remark is that if you want suppliers that sell the most different products, then you want to order by descending count (not ascending).

Finally, if you want to left join, then you should start from the suppliers and then bring in the products, not the other way around.

Consider:

select top 2 
    s.SupplierID,
    s.ContactName, 
    COUNT(*) as Supply
from Suppliers s
left join Products p on p.SupplierID = s.SupplierID
group by s.SupplierID, s.ContactName
order by Supply desc;

Upvotes: 1

Tim
Tim

Reputation: 5859

You're currently joining on two different fields:

on Suppliers.ContactName = Products.SupplierID

Presumably this should be as follows?

on Suppliers.SupplierID = Products.SupplierID

Upvotes: 0

Related Questions