I'm trying to create a new field based in another (sum field)

SELECT sum(price) AS old_price,
   old_price+100 AS total_price
FROM sales

This returns Unknown column 'old_price' in 'field list'

sum is just a example, my real issue is to use with a select inside this field

What am I doing wrong?


--edit I've used variables to reuse the alias, updated code:

SELECT @old_price := sum(price) AS old_price,
   @old_price+100 AS total_price
FROM sales

If you think that it is not a good practice, tell me.


You didn't do anything wrong. Unfortunately, referencing calculated columns in the field list is just not possible.

The usual workaround is to use self joins, joins to subselects or anything like that. But that would only qualify for more complex calculations. In your case, the solution would be to just write it the way Ryan mentioned, even if that turns the stomach of every programmer.

  • Yea, you're right.. and what about using variables? – Juliano Lima Mar 5 '14 at 19:39
  • 2
    Behavior for this is undefined, from the mysql 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. (from dev.mysql.com/doc/refman/5.0/en/user-variables.html) – Oliver Rahner Mar 5 '14 at 19:41

Yes, columns in the select-list cannot reference other columns by alias. This is standard SQL.

MySQL does not guarantee the order of evaluation of the select-list from left to right, so it would not be a recommended practice to use variables like you show.

Another workaround is to do computations and create column aliases in a derived table, and then reference them in the outer query. This is standard SQL and is reliable.

SELECT old_price, old_price + 100 AS total_price
    SELECT sum(price) AS old_price FROM sales
) AS t;
  • Great! what if I need to use one more derivated table for calculate? – Juliano Lima Mar 5 '14 at 20:32
  • You can keep nesting derived tables if you need to. – Bill Karwin Mar 5 '14 at 21:18

I think what you are wanting to do is:

SELECT sum(price) + 100 AS total_price FROM sales

Otherwise your query is looking for old_price as it's own column name when you are doing old_price+100


SELECT sum(price) AS old_price, sum(price) + 100 AS total_price FROM sales

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.