计算DISTINCT值的出现次数

我试图找到一个MySQL查询，将在特定字段中找到DISTINCT值，计数该值的出现次数，然后按计数顺序排列结果。

示例数据库

id         name
-----      ------
1          Mark
2          Mike
3          Paul
4          Mike
5          Mike
6          John
7          Mark

预期的结果

name       count
-----      -----
Mike       3
Mark       2
Paul       1
John       1

229052 次浏览

小开

最佳答案

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

小开

像这样的东西怎么样:

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

您选择名称和它出现的次数，但按名称分组，因此每个名称只选择一次。

最后，按降序排列次数，让最频繁出现的用户排在前面。

小开

刚刚将Amber的COUNT(*)更改为COUNT(1)以获得更好的性能。

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

小开

$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]'";
}

对我来说工作很顺利

GROUP BY DOES GIVE ALL DISTINCT VALUES