在SQL列中查找最常见的值

How can I find the most frequent value in a given column in an SQL table?

如何在SQL表的给定列中找到最常用的值？

For example, for this table it should return two since it is the most frequent value:

例如，对于此表，它应返回两个，因为它是最常见的值：

onetwotwothree

7 个解决方案

#1

120

SELECT       `column`,             COUNT(`column`) AS `value_occurrence`     FROM     `my_table`    GROUP BY `column`    ORDER BY `value_occurrence` DESC    LIMIT    1;

Replace column and my_table. Increase 1 if you want to see the N most common values of the column.

替换列和my_table。如果要查看列的N个最常见值，请增加1。

#2

Try something like:

尝试以下方法：

SELECT       `column`    FROM     `your_table`    GROUP BY `column`    ORDER BY COUNT(*) DESC    LIMIT    1;

#3

Let us consider table name as tblperson and column name as city. I want to retrieve the most repeated city from the city column:

让我们将表名称视为tblperson，将列名称视为城市。我想从城市列中检索最重复的城市：

 select city,count(*) as nor from tblperson        group by city          having count(*) =(select max(nor) from             (select city,count(*) as nor from tblperson group by city) tblperson)

Here nor is an alias name.

这里也不是别名。

#4

Below query seems to work good for me in SQL Server database:

以下查询在SQL Server数据库中似乎对我有用：

select column, COUNT(column) AS MOST_FREQUENTfrom TABLE_NAMEGROUP BY columnORDER BY COUNT(column) DESC

Result:

结果：

column          MOST_FREQUENTitem1           highest countitem2           second highest item3           third higest....

#5

For use with SQL Server.

用于SQL Server。

As there is no limit command support in that.

由于没有限制命令支持。

Yo can use the top 1 command to find the maximum occurring value in the particular column in this case (value)

在这种情况下，Yo可以使用top 1命令查找特定列中的最大值（值）

SELECT top1     `value`,    COUNT(`value`) AS `value_occurrence` FROM         `my_table`GROUP BY     `value`ORDER BY     `value_occurrence` DESC;

#6

Assuming Table is 'SalesLT.Customer' and the Column you are trying to figure out is 'CompanyName' and AggCompanyName is an Alias.

假设Table是'SalesLT.Customer'，你试图找出的Column是'CompanyName'而AggCompanyName是Alias。

Select CompanyName, Count(CompanyName) as AggCompanyName from SalesLT.Customergroup by CompanyNameOrder By Count(CompanyName) Desc;

#7

If you can't use LIMIT or LIMIT is not an option for your query tool. You can use "ROWNUM" instead, but you will need a sub query:

如果您不能使用LIMIT或LIMIT不是您的查询工具的选项。您可以改为使用“ROWNUM”，但是您需要一个子查询：

SELECT FIELD_1, ALIAS1FROM(SELECT FIELD_1, COUNT(FIELD_1) ALIAS1    FROM TABLENAME    GROUP BY FIELD_1    ORDER BY COUNT(FIELD_1) DESC)WHERE ROWNUM = 1

#1

120

SELECT       `column`,             COUNT(`column`) AS `value_occurrence`     FROM     `my_table`    GROUP BY `column`    ORDER BY `value_occurrence` DESC    LIMIT    1;