How to use count and group by at the same select statement

Question

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

Answer 1

This will do what you want (list of towns, with the number of users in each):

You can use most aggregate functions when using a GROUP BY statement (COUNT, MAX, COUNT DISTINCT etc.)