CODEWITHSUNDEEP
mysql
Mithilesh Kumar
Mithilesh Kumar

Reputation: 102

How to get count of State and city of country using SQL query from database?

By running this query:

select 
    c.name, count(s.name) as statecount,  
    sum(count(ci.name)) OVER() AS citycount 
from 
    countries c, states s, cities ci
where 
    ci.state_id = s.id 
    and s.country_id = c.id 
group by 
    s.name

I need to get an output in this style:

Country => statecount => citycount

Upvotes: 1

Views: 25190

Answers (2)

xQbert
xQbert

Reputation: 35333

Because countries can have multiple states and each state can have multiple cities when you join these 1 to many and 1 to many many your state count is inflated. So you need the distinct count of state. The city count is already unique to country and state, thus doesn't need the distinct. where as state is not unique to country city, thus distinct is needed. This of course assumes you want the count of unique states in each country.

SELECT c.name, count(distinct s.name) as statecount,  count(Ci.name) as CityCount
FROM countries c
INNER JOIN states s 
  on c.id = s.country_ID
INNER JOIN cities ci
  ON s.id = ci.state_id
GROUP BY C.name

Or keeping your old style join notation:

SELECT c.name, count(distinct s.name) as statecount,  count(ci.name) citycount 
FROM countries c,states s,cities ci
WHERE ci.state_id = s.id 
  and s.country_id = c.id 
GROUP BY s.name

Consider the following example: http://rextester.com/ZGYF56786

or pictorially below

See when the joins occur between country, state and city. state gets repeated because of the join to city, making state no longer unique in that column, by doing a distinct we only return a count of 2 states instead of 7, one for each record.

+-----+------------+-------------+
| USA | Illinois   | Chicago     |
| USA | Illinois   | Springfield |
| USA | Illinois   | Peoria      |
| USA | California | LosAngeles  |
| USA | California | Sacramento  |
| USA | California | SanDeigo    |
| USA | California | Hollywood   |
| USA | California | Oakland     |
|-----|------------|-------------|
|Name | statecount | Citycount   |
| USA | 2          | 7           | <-- Is this result correct? (i hope so)
| USA | 7          | 7           | <-- or this one? (then why bother just count(*) and only 1 count needed.
+-----+------------+-------------+

I would think you want the 1st result since there are only 2 states in USA table listed and 7 cities.

Upvotes: 4

Chris Allwein
Chris Allwein

Reputation: 2578

You don't need Over. This should just be a simple group-by.

SELECT c.name, COUNT(s.name) AS statecount,  COUNT(ci.name) AS citycount 
FROM countries c
     JOIN states s
        ON c.id = s.country_id
     JOIN cities ci
        ON s.id = ci.state_id
GROUP BY c.name

Upvotes: 0

Related Questions