SQL 如何在同一个select语句中使用count和group by
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2722408/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use count and group by at the same select statement
提问by Stavros
I have an sql select query that has a group by. I want to count all the records after the group by statement. Is there a way for this directly from sql? For example, having a table with users I want to select the different towns and the totalnumber of users
我有一个 sql select 查询，它有一个 group by。我想计算 group by 语句之后的所有记录。有没有办法直接从sql？例如，有一个用户表，我想选择不同的城镇和用户总数
select town, count(*) from user
group by town
I want to have a column with all the towns and another with the number of users in all rows.
我想要一列包含所有城镇，另一列包含所有行中的用户数。
An example of the result for having 3 towns and 58 users in total is :
总共有 3 个城镇和 58 个用户的结果示例是：
Town Count
Copenhagen 58
NewYork 58
Athens 58
回答by Oded
This will do what you want (list of towns, with the number of users in each):
这将执行您想要的操作（城镇列表，每个城镇的用户数量）：
select town, count(town)
from user
group by town
You can use most aggregate functionswhen using GROUP BY.
您可以使用大多数聚合函数使用时GROUP BY。
Update(following change to question and comments)
更新（随着问题和评论的变化）
You can declare a variable for the number of users and set it to the number of users then select with that.
您可以为用户数量声明一个变量并将其设置为用户数量，然后使用该变量进行选择。
DECLARE @numOfUsers INT
SET @numOfUsers = SELECT COUNT(*) FROM user
SELECT DISTINCT town, @numOfUsers
FROM user
回答by milkovsky
回答by ZhenYu Wang
The other way is:
另一种方式是：
/* Number of rows in a derived table called d1. */
select count(*) from
(
/* Number of times each town appears in user. */
select town, count(*)
from user
group by town
) d1
回答by sagits
If you want to order by count (sound simple but i can`t found an answer on stack of how to do that) you can do:
如果您想按计数订购（听起来很简单，但我找不到有关如何执行此操作的堆栈的答案），您可以执行以下操作：
SELECT town, count(town) as total FROM user
GROUP BY town ORDER BY total DESC
回答by Tommi
With Oracle you could use analytic functions:
使用 Oracle，您可以使用分析函数：
select town, count(town), sum(count(town)) over () total_count from user
group by town
Your other options is to use a subquery:
您的其他选择是使用子查询：
select town, count(town), (select count(town) from user) as total_count from user
group by town
回答by Rick James
Ten non-deleted answers; most do notdo what the user asked for. Most Answers mis-read the question as thinking that there are 58 users in eachtown instead of 58 in total. Even the few that are correct are not optimal.
十个未删除的答案；大多数人不会做什么的用户要求。大多数答案将问题误读为认为每个城镇有 58 个用户，而不是总共58 个用户。即使是少数正确的也不是最佳的。
mysql> flush status;
Query OK, 0 rows affected (0.00 sec)
SELECT province, total_cities
FROM ( SELECT DISTINCT province FROM canada ) AS provinces
CROSS JOIN ( SELECT COUNT(*) total_cities FROM canada ) AS tot;
+---------------------------+--------------+
| province | total_cities |
+---------------------------+--------------+
| Alberta | 5484 |
| British Columbia | 5484 |
| Manitoba | 5484 |
| New Brunswick | 5484 |
| Newfoundland and Labrador | 5484 |
| Northwest Territories | 5484 |
| Nova Scotia | 5484 |
| Nunavut | 5484 |
| Ontario | 5484 |
| Prince Edward Island | 5484 |
| Quebec | 5484 |
| Saskatchewan | 5484 |
| Yukon | 5484 |
+---------------------------+--------------+
13 rows in set (0.01 sec)
SHOW session status LIKE 'Handler%';
SHOW session status LIKE 'Handler%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 4 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 3 |
| Handler_read_key | 16 |
| Handler_read_last | 1 |
| Handler_read_next | 5484 | -- One table scan to get COUNT(*)
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 15 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 14 | -- leapfrog through index to find provinces
+----------------------------+-------+
In the OP's context:
在 OP 的上下文中：
SELECT town, total_users
FROM ( SELECT DISTINCT town FROM canada ) AS towns
CROSS JOIN ( SELECT COUNT(*) total_users FROM canada ) AS tot;
Since there is only one row from tot, the CROSS JOINis not as voluminous as it might otherwise be.
由于来自 的只有一行tot，因此CROSS JOIN它不像其他方式那样庞大。
The usual pattern is COUNT(*)instead of COUNT(town). The latter implies checking townfor being not null, which is unnecessary in this context.
通常的模式是COUNT(*)代替COUNT(town)。后者意味着检查town是否为非空，这在此上下文中是不必要的。
回答by Marcus
I know this is an old post, in SQL Server:
我知道这是 SQL Server 中的旧帖子：
select isnull(town,'TOTAL') Town, count(*) cnt
from user
group by town WITH ROLLUP
Town cnt
Copenhagen 58
NewYork 58
Athens 58
TOTAL 174
回答by Jur P
You can use DISTINCT inside the COUNT like what milkovsky said
您可以像milkovsky所说的那样在COUNT中使用DISTINCT
in my case:
就我而言：
select COUNT(distinct user_id) from answers_votes where answer_id in (694,695);
This will pull the count of answer votes considered the same user_id as one count
这将拉出被认为与一个计数相同的 user_id 的答案投票数
回答by Violendy Firdaus
If you want to select town and total user count, you can use this query below:
如果要选择城镇和总用户数，可以使用以下查询：
SELECT Town, (SELECT Count(*) FROM User) `Count` FROM user GROUP BY Town;
回答by Prakash
if You Want to use Select All Query With Count Option, try this...
如果您想使用 Select All Query With Count Option，试试这个...
select a.*, (Select count(b.name) from table_name as b where Condition) as totCount from table_name as a where where Condition
