如何使用计数和组在同一选择语句

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

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

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

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

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

小镇 数
哥本哈根 58
纽约 58
雅典 58
827216 次浏览
小开
最佳答案

这将完成你想要的(城镇列表，以及每个城镇的用户数量):

SELECT `town`, COUNT(`town`)
FROM `user`
GROUP BY `town`;
在使用GROUP BY语句时，可以使用大部分聚合函数 (COUNT， MAX， COUNT DISTINCT等)

< >强更新: 你可以为用户数量声明一个变量并将结果保存在那里，然后SELECT变量的值:

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
小开

另一种方法是:

/* 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) as total FROM user
GROUP BY town ORDER BY total DESC
小开

你可以使用COUNT(DISTINCT ...):

SELECT COUNT(DISTINCT town)
FROM user
小开

你可以像milkovsky说的那样在COUNT中使用DISTINCT

在我的例子中:

select COUNT(distinct user_id) from answers_votes where answer_id in (694,695);

这将拉的答案投票计数认为相同的user_id作为一个计数

小开

如果你想选择城镇和总用户数量，你可以使用下面的查询:

SELECT Town, (SELECT Count(*) FROM User) `Count` FROM user GROUP BY Town;
小开

试试下面的代码:

select ccode, count(empno)
from company_details
group by ccode;
小开

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

如果你想使用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
小开

10个未删除的答案;大多数不做什么用户要求。大多数回答错误地理解了这个问题，认为每一个镇有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%';

+----------------------------+-------+
| 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
+----------------------------+-------+

在OP的背景下:

SELECT  town, total_users
FROM       ( SELECT  DISTINCT town  FROM  canada ) AS towns
CROSS JOIN ( SELECT  COUNT(*) total_users  FROM  canada ) AS tot;

由于tot中只有一行，所以CROSS JOIN并不像其他情况下那样庞大。

通常的模式是COUNT(*)而不是COUNT(town)。后者意味着检查town是否为非空，这在此上下文中是不必要的。


提交答案