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