Wh0RU Wh0RU - 6 days ago 7
MySQL Question

MySQL query for grouping data

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

Answer

Try this :;

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