Wh0RU Wh0RU - 3 months ago 35
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)