How to use count and group by at the same select statement
count, group-by, sql
Solution
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 a `GROUP BY` statement (`COUNT`, `MAX`, `COUNT DISTINCT` etc.)
Update: You can declare a variable for the number of users and save the result there, and then `SELECT` the value of the variable:
DECLARE @numOfUsers INT
SET @numOfUsers = SELECT COUNT(*) FROM `user`;
SELECT DISTINCT `town`, @numOfUsers FROM `user`;
Problem
I have an SQL `SELECT` query that also uses a `GROUP BY`, I want to count all the records after the `GROUP BY` clause filtered the resultset. Is there any way to do this directly with SQL? For example, if I have the table `users` and 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 New York 58 Athens 58