CODEWITHSUNDEEP
mysqlsubqueryalias
user225269
user225269

Reputation: 10913

How to alias a field or column in MySQL?

I'm trying to do something like this. But I get an unknown column error:

SELECT SUM(field1 + field2) AS col1, col1 + field3 AS col3 from core

Basically, I want to just use the alias so that I won't need to perform the operations performed earlier. Is this possible in mysql?

Upvotes: 33

Views: 43147

Answers (6)

ship shuk - www.shipshuk.com
ship shuk - www.shipshuk.com

Reputation: 57

In case you are using it with aggregate function (group by) and if it doesn't work for you place the calculated column to the end with forward column referecing.

SELECT FNC2(AF), FNC1(A) AS AF,  B, C,  FROM Table GROUP BY ...

1st one doesn't work due to forward column referencing. Do this instead 

SELECT FNC1(A) AS AF, B, C, FNC2((SELECT AF)) FROM Table GROUP BY ...

Upvotes: 1

Moises
Moises

Reputation: 171

You can select the alias:

SELECT SUM(field1 + field2) AS col1, (select col1) + field3 AS col3 from core

This works.

Upvotes: 16

Ravi Parekh
Ravi Parekh

Reputation: 5594

select @code:= SUM(field1 + field2), @code+1 from abc;

But, please be aware of the following (from the MySQL 5.6 docs):

As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value within the same statement. For example, to increment a variable, this is okay:

SET @a = @a + 1;

For other statements, such as SELECT, you might get the results you expect, but this is not guaranteed. In the following statement, you might think that MySQL will evaluate @a first and then do an assignment second:

SELECT @a, @a:=@a+1, ...;

However, the order of evaluation for expressions involving user variables is undefined.

So, use at your own risk.

Upvotes: 39

Kamran Aslam
Kamran Aslam

Reputation: 123

select @code:= SUM(field1 + field2), (@code*1) from abc;

@code*1 covert into numeric expression and you can use anywhere like 

select @code:= SUM(field1 + field2), (@code*1)+field3 from abc;

Upvotes: 5

Andomar
Andomar

Reputation: 238116

Consider using a subquery, like:

SELECT col1
,      col1 + field3 AS col3 
FROM   (
       SELECT  field1 + field2 as col1
       ,       field3
       from    core
       ) as SubQueryAlias

Upvotes: 21

Denis de Bernardy
Denis de Bernardy

Reputation: 78463

Short answer is no:

mysql> select 1 as a, a + 1 as b;
ERROR 1054 (42S22): Unknown column 'a' in 'field list'

postgresql# select 1 as a, a + 1 as b;
ERROR:  column "a" does not exist

That said, some SQL implementations allow to use the aliases in where/group by/having clauses, e.g.:

postgresql# select 1 as a group by a; -- 1 row

Upvotes: 2

Related Questions