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
• 23,260 points
862 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
• 23,260 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
731 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, 2023 in Others by narikkadan
• 63,680 points
503 views
0 votes
1 answer

Excel VBA- How to loop through specific sheets in a workbook and format the same ranges in each sheet

Range(...) instructs VBA to always use the ...READ MORE

answered Mar 21, 2023 in Others by Kithuzzz
• 38,020 points
1,713 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 4,485 views
0 votes
1 answer

Using group by on multiple columns

Group By X means to put all those with ...READ MORE

answered Sep 3, 2022 in Data Science by narikkadan
• 63,680 points
708 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
• 63,680 points
717 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,240 points
3,073 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,240 points
5,616 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
• 23,260 points
997 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
• 23,260 points

edited Jun 20, 2023 by Khan Sarfaraz 11,819 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