I need to find the number of users in every district.
My DB Structure:
Table Name : countries
country_id country_name
1 India
2 Singapore
3 Malaysia
4 United States
Table Name : states
state_id state_name country_id
1 Tamil Nadu 1
2 Andhra 1
.
.
.
20 Arizona 4
Table Name : districts
district_id district_name state_id
1 Trichy 1
2 Chennai 1
3 Hyderabad 2
.
.
.
100 Phoenix 20
Table Name : user_addresses
user_address_id user_id doorno street ... districtName stateName countryName active
1 1 10 .... ... Trichy Tamil Nadu India 1
2 2 A12 .... ... Chennai Tamil Nadu India 0
3 3 A2 .... ... Chennai Tamil Nadu India 1
4 4 B2 .... ... Chennai Tamil Nadu India 1
5 41 89 .... ... Phoenix Arizona United States 1
user_addresses
table contain more than one address for a user. But only one is active. I need to find number of users in a district and country.
I used this query:
SELECT country_name, district_name, COUNT(*)
FROM districts as d
LEFT JOIN states as s USING (state_id)
LEFT JOIN countries as c USING (country_id)
LEFT JOIN user_addresses as ua on ua.districtName = d.district_name
WHERE ua.active=1
GROUP BY ua.district
I get all the districts in the user_addresses
and their count. But still I have some districts in districts
table for which I need to display the count as 0.
What I get:
country_name district_name COUNT(*)
India Trichy 1
India Chennai 2
United States Phoenix 1
What I need:
country_name district_name COUNT(*)
India Trichy 1
India Chennai 2
India Hyderabad 0
Singapore ... 0
Malaysia ... 0
... ... 0
... ... 0
United States Phoenix 1