CODEWITHSUNDEEP
mysqlsqldatabasedistinct
JimmyJ
JimmyJ

Reputation: 4441

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

Upvotes: 295

Views: 262744

Answers (4)

user20238354
user20238354

Reputation: 1

$sql ="SELECT DISTINCT column_name FROM table_NAME";

$res = mysqli_query($connection_variable,$sql);

while($row = mysqli_fetch_assoc($res))
{
$sqlgetnum = "select count(*) as count from table_NAME where column_name= '$row[column_name]'";
}

WORKED PROPERLY FOR ME

GROUP BY DOES GIVE ALL DISTINCT VALUES

Upvotes: -1

Pascal MARTIN
Pascal MARTIN

Reputation: 401172

What about something like this:

SELECT
  name,
  count(*) AS num
FROM
  your_table
GROUP BY
  name
ORDER BY
  count(*)
  DESC

You are selecting the name and the number of times it appears, but grouping by name so each name is selected only once.

Finally, you order by the number of times in DESCending order, to have the most frequently appearing users come first.

Upvotes: 24

Amber
Amber

Reputation: 527328

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

Upvotes: 519

aerin
aerin

Reputation: 22724

Just changed Amber's COUNT(*) to COUNT(1) for the better performance.

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

Upvotes: 9

Related Questions