Shane Shane - 4 months ago 22
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!

 SELECT Name, noofdistricts
      (  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