每个区的用户数包括0计数

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

SELECT country_name, district_name, COUNT(ua.active) 
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 
                              AND ua.active=1 
GROUP BY country_name, district_name

The general GROUP BY rule says:

If a GROUP BY clause is specified, each column reference in the SELECT list must either identify a grouping column or be the argument of a set function.

When LEFT JOIN, you usually don't put right side table's conditions in the WHERE clause, if you do you get inner join result. Move to ON clause to get true LEFT JOIN result!