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, 2022 in Others by gaurav
• 22,940 points
88 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, 2022 by gaurav
• 22,940 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, 2022 in Others by Kichu
• 19,050 points
266 views
0 votes
1 answer

How to merge two Tables and Add the amount at the same time?

Use SUMPRODUCT like this: =SUMPRODUCT(($A$2:$A$5=A10)*$B$2:$B$5)+ ...READ MORE

answered Jan 7 in Others by narikkadan
• 52,760 points
48 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,447 views
0 votes
0 answers
0 votes
0 answers

Using group by on multiple columns

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

Sep 2, 2022 in Data Science by Kithuzzz
• 28,520 points
91 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, 2022 in Database by narikkadan
• 52,760 points
152 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,396 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,730 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, 2022 in Others by gaurav
• 22,940 points
163 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, 2022 in Others by gaurav
• 22,940 points
4,552 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