Is it possible to reuse a computed field in a SELECT query?

Question

Is there a way to reuse the computed field in the 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 need to repeat a calculation that a very long SQL statement will do if I add 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.

Answer 1

Yes, you can reuse variables. Here's 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 this here: http://dev.mysql.com/doc/refman/5.0/en/user-variables.html

[Note: This behavior is undefined. According to MySQL docs:]

Generally, you should never assign a value to a user variable and read the value within the same statement. You can get the expected results, but this is not guaranteed.

Answer 2

In my testing, MySQL 5.5 works well:

 SELECT s.f1 + s.f2 as total_sale, s.f1 / (SELECT total_sale) as f1_percent FROM sales s 

Answer 3

Only cross-platform supported tools are using a view / 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 

Answer 4

You can use a subselect:

 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; 

Answer 5

You can use subqueries, for example:

 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 optimization, but I think it will become harder to read if you need more subqueries.

Answer 6

I tested the following and it seems to work all the time, maybe there is a reason for this because I predefined the variable @total_sales as a value, not a string, and did not redefine its type during the select operation

If I do the following

  set @total_sales = 0; SELECT @total_sale := s.f1 + s.f2 as total_sale, s.f1 / @total_sale as f1_percent FROM sales s 

Answer 7

I looked at the various answers here and did some experiments.

In particular, I am using MariaDB 10.1.

For the “simple” thing, you can do what Robert D. suggested in his comment:

 SELECT Price_Per_SqFt, (Price_Per_SqFt/2) AS col1, (SELECT col1 + 1) AS col2 FROM Items 

If you use some aggregate function with an inner join, you cannot use this, but you can combine this approach with the inner join approach as follows (NB VAT = "sales tax" ... and NB in ​​the financial data field usually have 4 decimal places, I think it's historical ...)

 SELECT invoices.invoiceNo, invoices.clientID, invoices.Date, invoices.Paid, invoicesWithSubtotal.Subtotal, ROUND( CAST( Subtotal * invoices.VATRate AS DECIMAL( 10, 4 )), 2 ) AS VAT, (SELECT VAT + Subtotal) AS Total FROM invoices INNER JOIN ( SELECT Sum( invoiceitems.Charge ) AS Subtotal, invoices.InvoiceNo FROM invoices INNER JOIN invoiceitems ON invoices.InvoiceNo = invoiceitems.InvoiceNo GROUP BY invoices.InvoiceNo ) invoicesWithSubtotal ON invoices.InvoiceNo = invoicesWithSubtotal.InvoiceNo 

I wanted to use the above to create a View to list invoices with their subtotals, VAT and totals ... it turned out that MariaDB (and almost certainly MySQL) does not allow nesting in the FROM , but this is easy to solve by creating the first View , which lists InvoiceNo and Subtotal , and then makes a second View , which refers to the first. In terms of quality, I generally don’t know how to arrange this double << 22>.