How to use calculation from select for another calculation

Question

I am trying to use a named calculation from one select into another,using Ver 15.1 Distrib 10.1.38-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2.

select f.price * t.price as result_price, result_price-30/30 as percentage from table f join table t on f.to_id = t.from_id where f.from_id = 12 and t.to_id=205;

It gives me an error "ERROR 1054 (42S22): Unknown column 'result_price' in 'field list'". How can I make it see result_price in select statement, or do I just simply make a duplification and write "(t.price*f.price - 30) / 30". Which one is more efficient?

try (f.price * t.price) as result_price and ((f.price * t.price)-30/30) as percentage — Roy Bogado
– Roy Bogado, Commented Aug 9, 2019 at 9:10
You can't use column aliases in the SELECT part of the query as they will not be evaluated in time. Make the duplication. — Nick
– Nick, Commented Aug 9, 2019 at 9:17
You could also use a subquery, but has more overhead than repeating the expression. — Rick James
– Rick James, Commented Aug 9, 2019 at 19:32

DevilaN · Accepted Answer · 2019-08-09 09:58:21Z

1

You can assign variable to calculation and then reuse it in query.

SELECT @result_price:=f.price*f.price AS result_price, (@result_price - 30)/30 AS percentage FROM ...

answered Aug 9, 2019 at 9:58

DevilaN

1,51414 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rick James Over a year ago

Caveat: In 8.0, doing assignments in a SELECT is deprecated. That means it will be removed in the future.

Collectives™ on Stack Overflow

How to use calculation from select for another calculation

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related