MySQL:计算不同值的出现次数。

I am trying to find a MySQL query that will find distinct values in a particular field, count the number of occurrences of that value and then order the results by the count.

我正在尝试查找一个MySQL查询，该查询将在特定字段中找到不同的值，计算该值出现的次数，然后按计数对结果排序。

example db

示例数据库

id         name
-----      ------
1          Mark
2          Mike
3          Paul
4          Mike
5          Mike
6          John
7          Mark

expected result

预期的结果

name       count
-----      -----
Mike       3
Mark       2
Paul       1
John       1

Thanks

谢谢

3 个解决方案

#1

335

SELECT name,COUNT(*) as count FROM tablename GROUP BY name ORDER BY count DESC;

#2

what about something like this :

像这样的东西怎么样:

select name, count(*) as num
from your_table
group by name
order by count(*) desc

ie, you are selecting the name and the number of times it appears ; but grouping by name so each name is selected only once.

也就是说，你在选择名字和出现的次数;但是根据名称分组，所以每个名称只选择一次。

Then, you order by number of times, desc ; to have the most frequently appearing users come first.

然后，你按次数订购，desc;让最经常出现的用户先出现。

#3

Just changed Amber's COUNT(*) to COUNT(1) for the better performance.

只是将Amber的COUNT(*)改为COUNT(1)以获得更好的性能。

SELECT name, COUNT(1) as count 
FROM tablename 
GROUP BY name 
ORDER BY count DESC;

#1

335

SELECT name,COUNT(*) as count FROM tablename GROUP BY name ORDER BY count DESC;

#2