Use Alias Columns to calculate another Alias Column

Question

select movie_id,title,budget,revenue,currency,
case
    when unit="Thousands" then (revenue-budget)/1000
    when unit="Billions" then (revenue-budget)*1000
    else revenue-budget
    end as profit_mill,
case 
    when currency="INR" then profit_mill*82.42
    else profit_mill
    end as profit_mill_usd
from movies
join financials
using (movie_id);

I would want to use profit_mill in my second case statement to normailise the profit into USD.

However, receive this error in MySQL :

Error Code: 1054. Unknown column 'profit_mill' in 'field list'

Answer 1

That's not possible, but by using With..As clause (Common Table Expressions) like

with mf as
(
  select movie_id,
         title,
         budget,
         revenue,
         currency,
         case
           when unit = "Thousands" then
            (revenue - budget) / 1000
           when unit = "Billions" then
            (revenue - budget) * 1000
           else
            revenue - budget
         end as profit_mill,
         case
           when currency = "INR" then
            profit_mill * 82.42
           else
            profit_mill
         end as profit_mill_usd
    from movies
    join financials
   using (movie_id)
 )
 select *,
        case
          when currency = "INR" then
           profit_mill * 82.42
          else
           profit_mill
        end as profit_mill_usd
   from mf