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
;