CODEWITHSUNDEEP
mysql
Rick Evans
Rick Evans

Reputation: 125

Order By Count in results

I'm having trouble working out how to order results by the count of results.What I would like to do is have the results as ordered by the amount of occurences of product:

Data:

userID | product
     1 | 3
     1 | 4
     1 | 5
     2 | 3
     2 | 5

Expected results:

product
3
5
4

The results also need to exlude the current product so I have solved this by using a subquery:

SELECT Product,userID FROM ProductTable WHERE userID IN(SELECT userID FROM productViewed WHERE Product="4817") AND Product<>"4817"

I have tried Count(product) with Group By and Order by but this doesn't seem to work.

Is this the correct way to achieve this ?

Thanks,

Rick

Upvotes: 0

Views: 52

Answers (2)

AbdulRahman Ansari
AbdulRahman Ansari

Reputation: 3057

Check this SQL Fiddle for your answer

Example

Query:

select product from ProductTable
group by product order by count(userId) desc;

In this query, add your sub-query to exclude the products as per requirement.

Hope this helps :)

Upvotes: 1

Zafar Malik
Zafar Malik

Reputation: 6844

Try below:

SELECT 
product FROM 
(SELECT 
product,COUNT(userid) AS cnt 
FROM producttable 
GROUP BY product) as a 
WHERE product<>'excluding product' 
ORDER BY cnt DESC;

Upvotes: 0

Related Questions