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 |
Best Answer
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.)Update: You can declare a variable for the number of users and save the result there, and then
SELECT
the value of the variable: