MySQL: Count occurances of distinct values

Question

Hi,

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.

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

Answer 1

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

Answer 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.