MySQL query for grouping data

Question

can anyone please guide me with writing MySQL query for following scenario.

The data in table is like this,

Table Name: user

user_id     country     city            age
----------------------------------------------
1           India       Mumbai          22
2           India       Mumbai          22
3           India       Delhi           22
4           India       Delhi           23
5           India       Chennai         23
6           China       Beijing         20
7           China       Beijing         20
8           China       Shanghai        20
9           USA         New York        30
10          USA         New York        30
11          USA         New York        30
12          USA         Los Angeles     31
13          USA         Los Angeles     31
14          USA         Los Angeles     40

I want result to be like this which is basically sum of all users in particular country's city having same age.

country     city            age     age_count
----------------------------------------------
India       Mumbai          22          2
India       Delhi           22          1
India       Delhi           23          1
India       Chennai         23          1
China       Beijing         20          2
China       Shanghai        20          1
USA         New York        30          3
USA         Los Angeles     31          2
USA         Los Angeles     40          1

Mohammad Yusuf · Accepted Answer · 2016-12-01 14:34:19Z

3

Try this :;

SELECT country,
       city,
       age,
       count(user_id) AS age_count
FROM user
GROUP BY country,
         city,
         round(age)

edited Dec 1, 2016 at 14:34

Mohammad Yusuf

17.1k12 gold badges60 silver badges88 bronze badges

answered Nov 28, 2012 at 13:15

Sashi Kant

13.5k9 gold badges50 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Borderless.Nomad Over a year ago

One more thing what if age has some discrepancy e.g. some columns it has 22 other have 22.0. Any way to solve this?

Sashi Kant Over a year ago

You can typecast the age field, do you want me to modify?

Borderless.Nomad Over a year ago

Yes that would be of great help. Also I want to order rows by age_count but country, city display should remain as it is.

Sashi Kant Over a year ago

Have updated my query, but it will slow down your execution since if there is an index in city, it wont be considered

Borderless.Nomad Over a year ago

I tried this before your update, I tried this before your update, Select country, city, Cast(age as UNSIGNED) as age, count(user_id) as age_count from user group by country, city, age But didn't worked. Anyway thanks for the help.

|

Z . · Accepted Answer · 2012-11-28 13:21:56Z

3

select country, city, age, count(*) age_count
from user 
group by country, city, age

edited Nov 28, 2012 at 13:21

answered Nov 28, 2012 at 13:15

Z .

12.8k2 gold badges34 silver badges59 bronze badges

1 Comment

Borderless.Nomad Over a year ago

Someone else is playing with ratings. Your query is just little slower than Sashi Kant's query. Fetching 10000 rows, Your query: 603.99 secs Sashi Kant query: 586.27 secs

Collectives™ on Stack Overflow

MySQL query for grouping data

2 Answers 2

10 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related