Search code examples
mysqlsqldatabasedistinct

Count the occurrences of DISTINCT values

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
Solution 
  • SELECT name,COUNT(*) as count 
FROM tablename 
GROUP BY name 
ORDER BY count DESC;