Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

Thanks.

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

share|improve this question

3 Answers 3

up vote 0 down vote accepted

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.

share|improve this answer
    
Yea, you're right.. and what about using variables? –  Juliano Lima Mar 5 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 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
FROM (
    SELECT sum(price) AS old_price FROM sales
) AS t;
share|improve this answer
    
Great! what if I need to use one more derivated table for calculate? –  Juliano Lima Mar 5 at 20:32
    
You can keep nesting derived tables if you need to. –  Bill Karwin Mar 5 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

EDIT:

SELECT sum(price) AS old_price, sum(price) + 100 AS total_price FROM sales
share|improve this answer
    
No, it is two separated columns, the first one is sum, and the second is sum+100 –  Juliano Lima Mar 5 at 19:28
    
See my edit; perhaps that will work for you. –  Ryan Mar 5 at 19:34
    
Yeah, that could work, but what if instead of a sum, I used a big select, then should I to paste it? see my edit. –  Juliano Lima Mar 5 at 19:36
    
See Oliver's comment. –  Ryan Mar 5 at 19:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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