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

0 votes

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

Nov 4 in Others by gaurav
• 22,040 points
30 views

1 answer to this question.

0 votes

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.)

answered Nov 7 by gaurav
• 22,040 points

Related Questions In Others

0 votes
0 answers

How is it possible for DynamoDB to support both Key-Value and Document database properties at the same time

As per DynamoDB's documentation, it supports both ...READ MORE

Apr 5 in Others by Kichu
• 19,040 points
200 views
0 votes
2 answers

How to use the flat button in Flutter?

FlatButton is really easy to use. Where ever ...READ MORE

answered Nov 9, 2020 in Others by Vijay
• 200 points

edited Nov 9, 2020 by MD 2,130 views
0 votes
0 answers
0 votes
1 answer
0 votes
0 answers

Using group by on multiple columns

I recognize the purpose of GROUP BY ...READ MORE

Sep 2 in Data Science by Kithuzzz
• 20,660 points
56 views
0 votes
1 answer

Using ORDER BY and GROUP BY together

Correctly use the  group by: select l.* from table ...READ MORE

answered Sep 20 in Database by narikkadan
• 37,660 points
73 views
0 votes
1 answer

how to get the count of non zero values in a row

In first part you should solve the ...READ MORE

answered Apr 4, 2018 in Tableau by Atul
• 10,230 points
2,300 views
0 votes
1 answer

How can I count the aggregate function in Tableau

Create two calculated fields one for 100's ...READ MORE

answered Apr 6, 2018 in Tableau by Atul
• 10,230 points
4,637 views
0 votes
1 answer

How to install a GUI on Amazon AWS EC2 or EMR with the Amazon AMI

The top-level script for installing the GUI ...READ MORE

answered Mar 9 in Others by gaurav
• 22,040 points
100 views
0 votes
1 answer

How to get password of the Amazon aws rds user?

Follow these procedures to update the master ...READ MORE

answered Mar 9 in Others by gaurav
• 22,040 points
3,081 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP