我有一个SQL SELECT查询,也使用GROUP BY, 我想统计GROUP BY子句过滤结果集后的所有记录。

有什么方法可以直接用SQL做到这一点吗?例如,如果我有用户表,想要选择不同的城镇和用户总数:

SELECT `town`, COUNT(*)
FROM `user`
GROUP BY  `town`;

我想有一个列的所有城镇和另一个与用户的数量在所有行。

一个有3个镇和58个用户的结果的例子是:

Town Count
Copenhagen 58
New York 58
Athens 58

当前回答

在Oracle中,你可以使用分析函数:

select town, count(town), sum(count(town)) over () total_count from user
group by town

你的其他选择是使用子查询:

select town, count(town), (select count(town) from user) as total_count from user
group by town

其他回答

另一种方法是:

/* 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

这将做你想做的(城镇列表,每个城镇的用户数量):

SELECT `town`, COUNT(`town`)
FROM `user`
GROUP BY `town`;

在使用GROUP BY语句时,可以使用大多数聚合函数 (COUNT, MAX, COUNT DISTINCT等)

更新: 你可以为用户数量声明一个变量并保存结果,然后选择变量的值:

DECLARE @numOfUsers INT
SET @numOfUsers = SELECT COUNT(*) FROM `user`;

SELECT DISTINCT `town`, @numOfUsers FROM `user`;

在Oracle中,你可以使用分析函数:

select town, count(town), sum(count(town)) over () total_count from user
group by town

你的其他选择是使用子查询:

select town, count(town), (select count(town) from user) as total_count from user
group by town

如果你想使用Select All Query With Count选项,试试这个…

 select a.*, (Select count(b.name) from table_name as b where Condition) as totCount from table_name  as a where where Condition

我知道这是一个旧的帖子,在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