How to alias a field or column in MySQL?

Question

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?

why you yous `col1` in `select` when `col1` is already an alias — xkeshav, May 21 '11 at 11:35
a field in the table with integer datatype, field1 and field2, and the alias col1 are integer as well. — user225269, May 21 '11 at 11:36
what is the problem in `SELECT SUM(field1 + field2 + field3) AS coll from core` — xkeshav, May 21 '11 at 11:36
@diEcho: I just want to reuse something that has already been computed earlier — user225269, May 21 '11 at 11:37
I think I'll just do the computation in php if this is not possible. Thanks for those who commented. — user225269, May 21 '11 at 11:45

score 39 · Answer 1 · edited Jun 20 '20 at 09:12

39

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.

edited Jun 20 '20 at 09:12

Community

1
1

answered May 21 '11 at 11:48

Ravi Parekh

5,253
9
46
58

1

That's sweet syntactic sugar. :-) – Denis de Bernardy May 21 '11 at 11:50
Sadly, this works brilliantly in a query, but not in a View :( – maxhugen Apr 29 '13 at 02:08
@kenn Worked fine for me, on Mysql! – Mirage Feb 16 '15 at 09:29
3

@Melvin RTFM http://dev.mysql.com/doc/refman/5.6/en/user-variables.html "...the order of evaluation for expressions involving user variables is undefined." In fact I get NULL with the above query. – kenn Feb 18 '15 at 02:50
1

@kenn, I added the caveat as an edit. Thanks for the heads up, this might bite people who are unaware of it. – insaner Jun 28 '16 at 05:59
This is working great when testing something simple, but when I apply this to a query that I'm using in production, I get unreliable results. The field value will be there and not there sometimes. – rclai Aug 02 '17 at 16:31

score 21 · Accepted Answer · answered May 21 '11 at 11:50

21

Consider using a subquery, like:

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

answered May 21 '11 at 11:50

Andomar

232,371
49
380
404

score 16 · Answer 3 · edited Sep 09 '16 at 03:56

16

You can select the alias:

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

This works.

edited Sep 09 '16 at 03:56

Pang

9,564
146
81
122

answered Apr 16 '15 at 13:29

Moises

171
1
2

2

It works. Same problem and same solution as this one: http://stackoverflow.com/questions/2077475/using-an-alias-in-sql-calculations – dxvargas Oct 19 '15 at 15:33
5

this works great in mysql.. not sure about other dbs. should be top answer – But those new buttons though.. Mar 09 '16 at 20:15
2

This does not work ('reference to group function' error) if the column you reference to is calculated based on a group function like in `SELECT MAX(ID) AS c1, (SELECT c1) AS c2 FROM log GROUP BY ID` - it works however if you replace `MAX(ID)` with `ID` in this example. – Christopher K. Jun 06 '18 at 10:05
@ChristopherK. is correct. I ran into this as well. I was defining a view, however, rather than just running a straight query. As such, I resolved this by nesting one view inside of the other. The first view uses the aggregate functions. The second (wrapper view) performs calculations over the top of that first one. Problem solved. – BuvinJ Oct 03 '18 at 00:33

score 5 · Answer 4 · answered Dec 22 '14 at 11:09

5

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;

answered Dec 22 '14 at 11:09

Kamran Aslam

123
2
9

I came here to vote you up, this works perfectly in our scenario. Replaced @code by a more hungarian/code style friendly variable and kept the AS. `@fVariable:=SUM(column) AS alias` (alias should be encased by backticks, SO doesn't render them properly. – ReSpawN Jun 07 '16 at 10:31
@ReSpawN It's undefined behaviour. It happened to give you the answer you expected that time. Since it's open source, certain read + write usages are safe for a particular build (if you don't consider that a contradiction in terms) if you inspect the code. – philipxy Aug 16 '17 at 01:51

score 2 · Answer 5 · answered May 21 '11 at 11:46

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

score 1 · Answer 6 · answered Jan 09 '19 at 02:21

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

How to alias a field or column in MySQL?

6 Answers6

Linked