Count the occurrences of DISTINCT values

mysql sql database distinct
JimmyJ picture JimmyJ · Aug 28, 2009 · Viewed 190k times · Source

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

Answer

Amber picture Amber · Aug 28, 2009 
SELECT name,COUNT(*) as count 
FROM tablename 
GROUP BY name 
ORDER BY count DESC;

Related questions

What's faster, SELECT DISTINCT or GROUP BY in MySQL?

If I have a table CREATE TABLE users ( id int(10) unsigned NOT NULL auto_increment, name varchar(255) NOT NULL, profession varchar(255) NOT NULL, employer varchar(255) NOT NULL, PRIMARY KEY (id) ) and I want to get all unique values of profession …

Read More
Selecting distinct 2 columns combination in mysql

I have a mysql table that looks like this: 1 value1 value2 3534 2 value1 value1 8456 3 value1 value2 3566 4 value1 value3 7345 5 value2 value3 6734 I need a query to select all the rows with distinct column 2 and 3, for example the output I want for this …

Read More
MySQL case-insensitive DISTINCT

Can anyone tell me how i can SELECT DISTINCT from my database without it being case-sensitive? My query is SELECT DISTINCT email FROM `jm_order` The results brings out all the emails in the table but repeats the ones with …

Read More