I want help in a query: What is the average population of the districts in each country?-CodePudding

I have only one City Table :

ID      Name          Country Code  District        Population
6       Rotterdam     NLD           Zuid-Holland    593321
3878    Scottsdale    USA           Arizona         202705
3965    Corona        USA           California      124966
3973    Concord       USA           California      121780
3977    Cedar Rapids  USA           Iowa            120758
3982    Coral Springs USA           Florida         117549
1613    Neyagawa      JPN           Osaka           257315
1630    Ageo          JPN           Saitama         209442

The Expected Result is :

 ------------- ----------------- 
| countrycode | avg(population) |
 ------------- ----------------- 
| JPN         |     xxxxxx      |
| NLD         |     xxxxxxx     |
| USA         |     xxxxxxx     |
 ------------- -----------------

I have used the shared code but was not getting the expected answer:

select avg(population)
from city
where countrycode='JPN' and 'USA' and 'NLD'
group by district;

The above code gives me a blank result " avg(population)" - blank.

I am using SQL workbench

CodePudding user response：

Try:

select countrycode, avg(population) as avg_population
from city
where countrycode in('JPN', 'USA', 'NLD')
group by countrycode;

CodePudding user response：

This should do the job :

select countrycode, avg(population)
from city
where countrycode= in('JPN','USA','NLD')
group by countrycode;