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?

Answers


Consider using a subquery, like:

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

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.


You can select the alias:

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

This works.


According to the spec Ravi Parekh's answer is not guaranteed to always work, as "order of evaluation for expressions involving user variables is undefined".

I found this answer after I tried to use a variable and got weird results.


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;

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

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 ...

Need Your Help

Handling ExecuteScalar() when no results are returned

c# oracle ado.net

I am using the following SQL query and the ExecuteScalar() method to fetch data from an Oracle database:

Change DateTime in the Microsoft Visual Studio debugger

c# visual-studio debugging datetime

What the.... How do I change the value of a DateTime in the debugger? I can change it, but I get an error when leaving the edit field; it cannot parse it.