Re: MySQL List based on a specific AVG function and condition

[Peter Brawley]

`lifeexpectancy` values are per-country. You need to decide whether you want the region averages to accurately represent regions by being weighted by country population, or just the relatively meaningless arithmetic average per-country regardless of population. I'll assume you want the more accurate weighted average.

-- make region populations easily available
create or replace view regionpops as
select region, sum(population) as regionpop
from country
group by region;

-- regional average life expectancies
select region, sum( a.lifeexpectancy * a.population / b.regionpop )
from country a
join regionpops b using(region)
group by region;

Now that query becomes the subquery for your answer. Here I added the lifeexpectancy values to the Select list as a check on the calculation making sense:

select region, sum(population) as popsum, a.lifeexpectancy, b.regionLE
from country a
join (
  select region, sum( a.lifeexpectancy * a.population / b.regionpop ) as regionLE
  from country a
  join regionpops b using(region)
  group by region
) b using(region)
where a.lifeexpectancy < b.regionLE
group by region;
+---------------------------+------------+----------------+----------+
| region                    | popsum     | lifeexpectancy | regionLE |
+---------------------------+------------+----------------+----------+
| Baltic Countries          |    2424200 |           68.4 | 68.82284 |
| British Islands           |    3775100 |           76.8 | 77.64641 |
| Caribbean                 |    8316000 |           64.5 | 69.21611 |
| Central Africa            |   19436000 |           38.3 | 48.08958 |
| Central America           |   29461000 |           70.9 | 70.99977 |
| Eastern Africa            |   47507000 |           37.6 | 45.18690 |
| Eastern Asia              | 1304259000 |           71.4 | 72.30971 |
| Eastern Europe            |   35026400 |           70.9 | 71.42139 |
| Melanesia                 |    4807000 |           63.1 | 64.34627 |
| Micronesia                |     297000 |           59.8 | 70.55470 |
| Middle East               |   57449000 |           66.4 | 67.15592 |
| Nordic Countries          |    4521500 |           78.4 | 79.27854 |
| North America             |     128000 |           76.9 | 79.37417 |
| Northern Africa           |   68763000 |           63.3 | 66.50646 |
| Polynesia                 |     200000 |           71.1 | 72.39442 |
| South America             |  179305000 |           63.7 | 67.03645 |
| Southeast Asia            |   63097000 |           46.0 | 65.85890 |
| Southern Africa           |    3348000 |           39.3 | 50.34519 |
| Southern and Central Asia |  334412000 |           45.9 | 62.13517 |
| Southern Europe           |   34507800 |           71.6 | 77.55884 |
| Western Africa            |   63427000 |           50.2 | 50.35628 |
| Western Europe            |  100931200 |           77.8 | 77.98879 |
+---------------------------+------------+----------------+----------+