New field based on sum field

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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.

Method 2

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;

Method 3

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply