Using Column Alias in Same SELECT Clause

Question

Is there anyway to use a column alias in the same SELECT clause which it is being assigned? For example:

SELECT ord_id, candy_id, price, quantity, 
price * quantity AS ext_cost, ext_cost * @tax_rate

returns an error because MySQL does not recognize "ext_cost" in the ext_cost * @tax_rate query. If it is not possible, is possible to return a table with everything listed in the first query without having to write something like this?

SELECT ord_id, candy_id, price, quantity, 
price * quantity AS ext_cost, (price * quantity) * @tax_rate

Basically, I was just wondering if there was anyway to reuse ext_cost in the SELECT query.

Answer 1

No there isn't a way to refer to aliases, but you can assign the expression to a variable, and then refer to the variable in the same select clause.

Inside a select statement variable assignment is always done by the infix operator :=. *In a SET statement, it can be either = or :=.

e.g.

SELECT 
    ord_id
  , candy_id
  , price
  , quantity
  , @exc_cost := price * quantity AS exc_cost
  , @exc_cost * @tax_rate AS my_favourite_field
...
<FROM CLAUSE>

You can also conditionally perform variable assignment.

e.g.

IF(quantity > 90, 
     @exc_cost := price * quantity * 0.95
   , @exc_cost := price * quantity) AS exc_cost

Note 1: In the absence of aggregate measures & group by clause, the variables are evaluated according to column order:

SELECT @t, @t+2 FROM (SELECT @t := 1) a

produces the output

@t   @t+2
 1      3

Answer 2

In MySQL you CAN reference a select column alias in the same select, as long as it precedes the reference point.

SELECT 
    ord_id,
    candy_id,
    price,
    quantity, 
    price * quantity AS ext_cost,
    (SELECT ext_cost) * @tax_rate as retail_rate

Now if only I knew how to do this in postgres.

Answer 3

Using a subquery

SELECT t1.ord_id, t1.candy_id, t1.price, t1.quantity, t2.ext_cost, t2.ext_cost * @tax_rate
FROM table1 t1
JOIN (SELECT t.ord_id, t.price * t.quantity AS ext_cost FROM table1 t) t2
ON t2.ord_id = t1.ord_id