I was writing a code in mysql using the standard database of world to figure populate a list of countries along with the language most commonly spoken. The table I am using is the standard tables within world – city, country and countrylanguage. The output is satisfactory for most part, however, for Japan, the code gives me an incorrect value (most spoke language should be Japanese (99.1%), however my code gives me Ainu (0%). Not sure where I am going wrong. Can someone help?
select country.name as country, city.countrycode, count(city.name) as cities, country.population, countrylanguage.language, percentage from city inner join country on city.countrycode=country.code inner join countrylanguage on country.code=countrylanguage.countrycode where countrylanguage.percentage in (select max(percentage) from countrylanguage group by language) group by country.name order by country.name ;
Leave an answer