How to use count and group by at the same select statement
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
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 :
Town Count
Copenhagen 58
NewYork 58
Athens 58
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 .
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
您可以使用COUNT(DISTINCT ...) ：
SELECT COUNT(DISTINCT town)
FROM user
另一种方式是：
/* 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
上一篇: C ++ 0x初始化程序列表示例