Select top distinct results ordered by frequency

sql mysql count distinct

Albert M. · Aug 1, 2009 · Viewed 19.6k times · Source

My table has two columns: id and name. The data looks like this:

id | name
----------
1    Jeff
2    Sam
3    Carl
4    Sam
5    Carl
6    Jeff
7    Dave
8    Jeff

What I want to obtain is this:

name | frequency
----------------
Jeff      3
Carl      2
Sam       2
Dave      1

Essentially, I need an SQL query that counts the unique names within the table, and sorts them by their frequnecy. I'm using MySQL if it matters.

Thank-you.

Answer

I haven't tested it, so the syntax might not be perfect, but what about something like this :

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

Should give you unique names and the corresponding number of time they appeat in the table, ordered by that number