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:
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;