Select top distinct results ordered by frequency

Question

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.

Solution 1

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 times each name appears in the table, ordered by that number.

Solution 2

You need to use a GROUP BY:

SELECT name, COUNT(*) as frequency
FROM name_table
GROUP BY name
ORDER BY COUNT(*) DESC;

This will GROUP BY name (any non-aggregate columns needs to be named in the GROUP BY clause) and then COUNT the frequency of each name.

If you want only the top 25, you can then proceed to add a LIMIT clause as such:

SELECT name, COUNT(*) as frequency
FROM name_table
GROUP BY name
ORDER BY COUNT(*) DESC
LIMIT 25;

More information about the GROUP BY clause is available in the MySQL Manual:

12.2.8 SELECT Syntax