Can I reuse a calculated field in a SELECT query?
Yes, you can reuse variables. This is how you do it:
SELECT
@total_sale := s.f1 + s.f2 as total_sale,
s.f1 / @total_sale as f1_percent
FROM sales s
Read more about it here: http://dev.mysql.com/doc/refman/5.0/en/user-variables.html
[Note: This behavior is undefined. According to the MySQL docs:]
As a general rule, you should never assign a value to a user variable and read the value within the same statement. You might get the results you expect, but this is not guaranteed.
The following seems to work well in my testing on MySQL 5.5:
SELECT
s.f1 + s.f2 as total_sale,
s.f1 / (SELECT total_sale) as f1_percent
FROM sales s
Only cross-platform supported means is by using a derived table/inline view:
SELECT x.total_sale,
x.f1 / x.total_sale as f1_percent
FROM (SELECT s.f1,
s.f1 + s.f2 as total_sale,
FROM sales s) x
You can use a sub-select:
select tbl1.total_sale,
tbl1.f1/tbl1.total_sale as f1_percent
from (select s.f1+s.f2 AS total_sale,
s.f1
from sales s) as tbl1;
You can use subqueries, like this:
SELECT
h.total_sale,
s.f1 / h.total_sale AS f1_percent
FROM sales s,
(SELECT id, f1 + f2 AS total_sale FROM sales) h
WHERE
s.id = h.id
Edit:
fixed cartesian product, assuming the primary key is id
.
This should be equivalent to OMG Ponies' solution after optimizing, but I think it will become harder to read if you need more subqueries.