CODEWITHSUNDEEP
sqlfunctionaggregategrouping
Toph Hughes
Toph Hughes

Reputation: 111

Sorting results of aggregate function

This code:

SELECT ProductID, COUNT(ProductID) AS Occurrences
FROM OrderDetails
Group By ProductID;

is my attempt to take the productIDs that occur in an orderDetails table, count how many orders contain that product, and then sort the productID counts descending. In layman's terms, I want to show which products are the most popular, by showing their count in descending order so that the products that "sold" the most will occur at the top. The issue I have is that when I tried to use "Group by" with "Occurrences", it spat out an error that I cannot use aggregate functions with group by. If I group by ProductID, it shows the counts all over the place, which isn't the most useful way to present the information.

Upvotes: 1

Views: 44

Answers (2)

Anand
Anand

Reputation: 1123

What you really want to use if you want to know the popularity of the products is SUM not COUNT:

SELECT ProductID,
       sum(Qty) AS Occurrences
FROM   OrderDetails
Group  By ProductID
Order  By sum(Qty) DESC;

COUNT will give you interesting results. Or you could use count of orders like this:

select ProductID
       count(distinct OrderNumber) as NumberOfOrders
from OrderDetails
group by ProductID
order by count(distinct OrderNumber) desc;

Upvotes: 1

dana
dana

Reputation: 18155

This should work:

SELECT ProductID, COUNT(ProductID) AS Occurrences
FROM OrderDetails
Group By ProductID
Order By COUNT(ProductID) DESC;

Upvotes: 2

Related Questions