Skip to content
Advertisement

Counting orders by supplier results in SQL error

I am trying to get how many orders each supplier have, by showing two columns, CompanyName and number of orders. And then Sorting them by descending order.

My SQL:

SELECT DISTINCT(sp.CompanyName), COUNT(DISTINCT o.OrderID)
FROM Suppliers AS sp
INNER JOIN Products AS p ON sp.SupplierID = p.SupplierID
INNER JOIN Orders AS o ON od.OrderID = o.OrderID
INNER JOIN [Order Details] AS od ON p.ProductID = od.ProductID
                                 AND o.OrderID = od.OrderID
ORDER BY o.OrderDate DESC

But I get the following errors:

Msg 145, Level 15, State 1, Line 65
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Msg 8120, Level 16, State 1, Line 65
Column ‘Suppliers.CompanyName’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

My tables:

enter image description here

Advertisement

Answer

Something like this should be the correct one as mentioned in the comments in the post.

SELECT sp.CompanyName, COUNT(DISTINCT o.OrderID) as orders
FROM Suppliers as sp
INNER JOIN Products as p
ON sp.SupplierID = p.SupplierID
INNER JOIN [Order Details] as od
INNER JOIN Orders as o
ON od.OrderID = o.OrderID
ON p.ProductID = od.ProductID
AND 
o.OrderID=od.OrderID
GROUP BY sp.CompanyName;
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement