Is there a way to reuse a calculated field within a mysql statement. I get the error "unknown column total_sale" for:
SELECT s.f1 + s.f2 as total_sale, s.f1 / total_sale as f1_percent FROM sales s
or do I have to repeat the calculation, which would make for a very long SQL statement if I added all the calculations I need.
SELECT s.f1 + s.f2 as total_sale, s.f1 / (s.f1 + s.f2) as f1_percent FROM sales s
of course I can do all the calculations in my php program.
About Calculated Fields A calculated field becomes a new field in the pivot table, and its calculation can use the sum of other fields. Calculated fields appear with the other value fields in the pivot table.
When you create a calculated field in Access, you can perform almost any available function. You can also use any available query field or data entered by hand as values for the calculations.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With