我有一个具有分组依据的 sql 选择查询。我想统计分组后的所有记录。有没有办法直接从sql中解决这个问题?例如,有一张用户表我想选择不同的城镇和用户总数
select town, count(*) from user
group by town
我想有一列包含所有城镇,另一列包含所有行中的用户数。
共有 3 个城镇和 58 个用户的结果示例如下:
Town Count
Copenhagen 58
NewYork 58
Athens 58
这将做你想要的(城镇列表,每个城镇的用户数量):
select town, count(town)
from user
group by town
在使用 GROUP BY
时,您可以使用大多数 aggregate functions:
(COUNT
、MAX
、COUNT DISTINCT
等)
更新(在更改问题和评论之后)
您可以为用户数声明一个变量并将其设置为用户数,然后使用它进行选择。
DECLARE @numOfUsers INT
SET @numOfUsers = SELECT COUNT(*) FROM user
SELECT DISTINCT town, @numOfUsers
FROM user
您可以使用 COUNT(DISTINCT ...)
:
SELECT COUNT(DISTINCT town)
FROM user
town
列,并且 COUNTs
是错误的(城镇而不是用户)。
COUNT(DISTINCT ...)
确实适用于 GROUP BY
,只需确保您的分组字段出现在 SELECT
语句中。示例:SELECT COUNT(DISTINCT town), street FROM user GROUP BY street
另一种方式是:
/* 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
十个未删除的答案;大多数人不做用户要求的事情。大多数答案误读了这个问题,认为每个城镇有 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
是否不为空,这在这种情况下是不必要的。
使用 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 town, count(town) as total FROM user
GROUP BY town ORDER BY total DESC
您可以在 COUNT 中使用 DISTINCT,就像 Milkovsky 所说的那样
就我而言:
select COUNT(distinct user_id) from answers_votes where answer_id in (694,695);
这将拉取被认为与 user_id 相同的答案票数作为一个计数
我知道这是 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
IFNULL
而不是 ISNULL
)导致每个城镇的数字不同;用户想要总数。根据问题,总数是 58,而不是 174。
如果要选择城镇和总用户数,可以使用以下查询:
SELECT Town, (SELECT Count(*) FROM User) `Count` FROM user GROUP BY Town;
User
中没有重复的“用户”。
如果您想使用带计数选项的全选查询,试试这个...
select a.*, (Select count(b.name) from table_name as b where Condition) as totCount from table_name as a where where Condition
试试下面的代码:
select ccode, count(empno)
from company_details
group by ccode;
*
... OP 回答了他自己的问题,但似乎甚至没有测试它,我只是在验证它是正确的 :) fredosaurus.com/notes-db/select/groupby.html