Reputation: 271934

How do I write this GROUP BY query in MYSQL?

Suppose I have a column called "fruits" I want to select all of the top fruits, ranked by fruits (and group by + count).

Fruits:
orange
orange
apple
banana
apple
apple

In this case, the select statement would return:

apple, 3
orange, 2
banana, 1

Upvotes: 1

Answers (4)

Reputation: 811

Tested:

select fruits, count(fruits)
from fruit
group by fruits
order by 2 desc

Upvotes: 1

Reputation: 588

SELECT fruitname, COUNT(*) AS ttl
FROM fruits 
GROUP BY fruitname 
ORDER BY ttl DESC

Upvotes: 2

Reputation: 4050

select fruits, count(fruits)
from table
group by fruits
order by count(fruits) desc

Upvotes: 4

Reputation: 28016

Untested:

SELECT 
   fruit_name, 
   COUNT(fruit_id)
FROM
   fruit
GROUP BY
   fruit_name
ORDER BY
   COUNT(fruit_id) DESC

Upvotes: 4