Taking the top 5 suppliers based on sales from Northwind database-CodePudding

How can I take the top 5 suppliers based on sales?

I have the following database:

I have tried multiple solutions this is the closest one but I know it's wrong because of a single supplier appearing more than once.

SELECT TOP 5 
    (od.UnitPrice * (1 - od.Discount) * od.Quantity) total_sales,    
    od.Quantity, od.UnitPrice, od.Discount, S.ContactName
FROM
    [Order Details] od
INNER JOIN 
    Products p ON p.ProductID = od.ProductID
INNER JOIN 
    Suppliers s ON s.SupplierID = p.SupplierID
ORDER BY 
    total_sales DESC

The sum of the sales is provided by this formula:

SUM(UnitPrice * (1 - Discount) * Quantity)

Any help would be greatly appreciated!

CodePudding user response：

You need to group by supplier

SELECT TOP (5) 
    s.CompanyName,
    s.ContactName,
    SUM(od.UnitPrice * (1 - od.Discount) * od.Quantity) total_sales,
FROM
    Suppliers s
INNER JOIN 
    Products p ON s.SupplierID = p.SupplierID
INNER JOIN
    [Order Details] od ON p.ProductID = od.ProductID
GROUP BY
    s.SupplierID,
    s.CompanyName,
    s.ContactName
ORDER BY 
    total_sales DESC;

Note how the primary key of Suppliers is in the grouping even though it is not selected.