mysql - 确定返回的最高结果总量

Question

我想从一个查询中确定两件事:

1. 表格中最常见的列
2. 在查询表时找到此类列的次数

示例表:

user_id      some_field1                data2                data1                data

The above would return user_id # 1 as being the most prevalent in the table, and it would return (2) for the total amount of times that it was located in the table.

I have done my research and I came across two types of queries.

1. GROUP BY user_id ORDER BY COUNT(*) DESC
2. SUM

The problem is that I can't figure out how to use these two queries in conjunction with one another. For example, consider the following query which successfully returns the most prevalent column.

$top_user = "SELECT user_id FROM table_name GROUP BY user_id ORDER BY COUNT(*) DESC";

上述查询根据上面显示的示例表返回“1”。现在，我希望能够针对在表中找到 user_id (1) 的总次数返回“2”。

这有可能吗？

谢谢，

埃文

Answer 1

您可以在 SELECT 列表中包含 count(*):

SELECT user_id, count(*) as totaltimes from table_name 
         GROUP BY user_id ORDER BY count(*) DESC;

如果你只想要第一个:

SELECT user_id, count(*) as totaltimes from table_name 
         GROUP BY user_id ORDER BY count(*) DESC LIMIT 1;