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.

you can possibly check http://stackoverflow.com/questions/7072479/reusing-alias-in-select — Paul Lo, Mar 04 '15 at 16:59

Haleemur Ali · Answer 1 · 2015-03-04T19:10:24.230

36

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

edited Mar 04 '15 at 19:10

answered Mar 04 '15 at 17:07

Haleemur Ali

26,718
5
61
85

Won't the variable only be assigned after the row is processed? – Dan Field Mar 04 '15 at 18:36
1

for better understanding of it refer : https://stackoverflow.com/questions/16715504/mysql-define-a-variable-within-select-and-use-it-within-the-same-select – Arrow Feb 28 '20 at 11:50
The approach you took here is great but your answer would be better if you removed the false statement at the beginning: "*No there isn't a way to refer to aliases*". – But those new buttons though.. Sep 08 '21 at 13:33
Why do you say it's false, MySQL does not support lateral aliasing, and the approach shown in this post comes with major caveats – Haleemur Ali Sep 08 '21 at 14:42

score 16 · Answer 2 · answered Apr 27 '21 at 15:39

16

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.

answered Apr 27 '21 at 15:39

WiR3D

1,465
20
23

You can use lateral joins in postgres for aliasing expressions – Haleemur Ali May 04 '21 at 18:01
@HaleemurAli yes you can, but it's nowhere near as readable or easy, and has completely different behaviour. – WiR3D May 05 '21 at 11:58
So simple and working but, How Can I do this in SQL Server?? – Ammar Ismaeel Jan 13 '22 at 13:02
What is this technique called, and is it described in the MySQL docs? – Garland Pope May 20 '23 at 04:41
FYI: in Snowflake, you can simply reference the alias in subsequent column definitions. No funky syntax required. Very nice. – Garland Pope May 20 '23 at 04:46

score 2 · Answer 3 · answered Mar 04 '15 at 17:07

2

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

answered Mar 04 '15 at 17:07

Dan Field

20,885
5
55
71

Thanks for that. I've always tried avoiding subqueries because I have problems with them, but I guess I'll have to just learn how to deal with them one day. – Blobert Mar 04 '15 at 17:18

Using Column Alias in Same SELECT Clause

3 Answers3

Linked

Related