How to use a column alias immediately after it's been named in a SELECT clause?

Question

I created a column alias in line 7 called total_city_pop.

Then, in line 8, I tried to use that alias in a new calculation.

But, I received an error message that says "column "total_city_pop" does not exist LINE 8: (ci.city_proper_pop + ci.metroarea_pop) / total_city_pop AS ... ^"

Why can't I use the alias name after I created it?

Here's the code --

SELECT co.code, ci.name AS capital_city, ci.city_proper_pop, ci.metroarea_pop, ci.urbanarea_pop,
    CASE
        WHEN (ci.city_proper_pop + ci.metroarea_pop + ci.urbanarea_pop) IS NULL
        THEN (ci.city_proper_pop + ci.urbanarea_pop)
        ELSE (ci.city_proper_pop + ci.metroarea_pop + ci.urbanarea_pop) END AS total_city_pop, 
    (ci.city_proper_pop + ci.metroarea_pop) / total_city_pop AS percent_outside_urbanarea 
FROM countries AS co 
    INNER JOIN cities AS ci ON co.capital = ci.name 
    WHERE continent LIKE '%America%' OR continent LIKE 'Europe'     
    ORDER BY total_city_pop DESC 
    LIMIT 10;

Thank you.

Answer 1

You need to repeat the expression in the subquery. However, I would suggest you simplify the expression using COALESCE():

SELECT co.code, ci.name AS capital_city, ci.city_proper_pop, ci.metroarea_pop, ci.urbanarea_pop,
       (ci.city_proper_pop + ci.metroarea_pop + COALECE(ci.urbanarea_pop, 0) ) as  total_city_pop, 
       (ci.city_proper_pop + ci.metroarea_pop) / (ci.city_proper_pop + ci.metroarea_pop + COALECE(ci.urbanarea_pop, 0) ) AS percent_outside_urbanarea 
FROM countries co INNER JOIN
     cities ci
     ON co.capital = ci.name 
WHERE continent LIKE '%America%' OR continent LIKE 'Europe'     
ORDER BY total_city_pop DESC 
LIMIT 10;

The most recent versions of MySQL support computed columns. These would allow you to put the computation in the definition of the table.