Shane Shane - 1 month ago 7
SQL Question

SQL Query: Number of Districts each Country has?

Trying to write a query that will provide the name of the country and the number of districts that country has as well order the results by the
number of districts from highest to lowest.The problem is that I can't figure out how to specifically count the number of districts for each country and end up getting the total number of districts beside one country, and nothing else...

The two tables are Country (which contains Name, Code) and City (which contains Name, District, CountryCode).

Country.Code and City.CountryCode are the same, that's what the two tables have in common.

THANKS so much for any help!

Answer
 SELECT Name, noofdistricts
   FROM 
      (  SELECT c.Name,COUNT( ci.District ) AS noofdistricts
           FROM Country c
           JOIN City ci
             ON c.code = ci.countrycode
          GROUP BY c.Name
       ) 
 ORDER BY Name,noofdistricts DESC
 ;