Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can I reuse a calculated field in a SELECT query?

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.

like image 473
sdfor Avatar asked May 22 '11 01:05

sdfor


People also ask

Can you use a calculated field in another calculated field?

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.

Why would a calculated field be useful in a query?

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.


1 Answers

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.

like image 190
rzetterberg Avatar answered Oct 12 '22 22:10

rzetterberg