如何在同一个select语句中使用count和group

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 total number of users

我有一个sql select查询，它有一个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

10 个解决方案

#1

229

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 functions when 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

#2

125

You can use COUNT(DISTINCT ...) :

你可以使用COUNT(明显的…):

SELECT COUNT(DISTINCT town) 
FROM user

#3

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

#4

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

#5

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

#6

You can use DISTINCT inside the COUNT like what milkovsky said

你可以像米尔科夫斯基所说的那样，在伯爵内部使用不同的词

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的应答投票数

#7

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

#8

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;

#9

Try the following code:

试试下面的代码:

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

#10

if You Want to use Select All Query With Count Option, try this...

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

#1

229