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 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 ;