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

Question

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

Answer 1

If I'm understanding the problem correctly, it's that the original table "user" has 58 users in it that, first, you want to have grouped into towns (for example, with three towns one town will have 20 users, another 30 and another 8), but you also want the total available as well.

There are a few ways to do this, but I think putting a "count" function in the select statement will do what you need. It essentially gets the total without the grouping and puts it back in the "grouped" table.

Here's an example:

select town,

count(*) users_per_town,

(select count(*) from user) total_users

from user

group by town

You can view this in action at the following sqlfiddle link: http://sqlfiddle.com/#!7/29558/1