Count the occurrences of DISTINCT values
MysqlSqlDatabaseDistinctMysql Problem Overview
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
Mysql Solutions
Solution 1 - Mysql
SELECT name,COUNT(*) as count
FROM tablename
GROUP BY name
ORDER BY count DESC;
Solution 2 - Mysql
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.
Solution 3 - Mysql
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;