Can I reuse a calculated field in a SELECT query?

Question

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.

Havana

"of course I can do all the calculations in my php program" - That wouldn't be the worst idea. – Excision

Answer 1

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.

Lorislorita

@OMG Ponies Mind elaborating? "@total_sale :=" is that not enough declaration? – Lorislorita

That's setting the variable, not declaring it. Declaring means either using a SET statement or a derived table/inline view. – Subcontraoctave

"You can also assign a value to a user variable in statements other than SET. In this case, the assignment operator must be := and not = because the latter is treated as the comparison operator = in non-SET statements:" – Lorislorita

This is great, thanks! Are there any performance implications? Is it better to use mysql for calculations rather than php? – Havana

@sdfor: Doing the calculations directly in the query will be faster, instead of looping through the results again in php. – Lorislorita

I was mistaken. I hadn't seen MySQL variables used like this, but confirmed it worked on MySQL 5.1.x – Subcontraoctave

@OMG Ponies No worries! I also tested it straight away just to be sure, hehe. Tested on MySQL 5.1.49. – Lorislorita

@OMGPonies (And others) This is unsupported: "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." It works, as in, it runs. But the results might not be correct. – Lilytrotter

Downvoted since, as already pointed out, the MySQL documentation say that is a bad thing reusing variables in the same statement. – Celestyna

Downvoted for the same reason as called out by @Celestyna above. – Sander

I don't really understand the rationale for downvoting this answer, since it answers the specific question and it also educates the user why it's a bad idea to reuse variables (thanks @Ariel). – Lorislorita

When I try to use this with a group function (e.g. SUM or COUNT, I get the counterintuitive and unhelpful result that the variables always have the value of the previous row, not the row that I'm on. It's unsupported for good reason and should be avoided. – Jell

Apparently this capability has been deprecated and will be removed in time. – Ashlan

Answer 2

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

Triumph

I like this. By far the best readability from all answers and no warnings about using it in the mysql documentation. However it cannot be used to reference group results such as those you get with SUM or COUNT. – Yan

As the current accepted answer could not work everytime, this sould be the accepted answer – Valoniah

I just tested on MariaDB 10.1.24 with the following and it worked perfectly. SELECT Price_Per_SqFt, (Price_Per_SqFt/2) AS col1, (SELECT col1 + 1) AS col2 FROM Items LIMIT 100 – Triumph

Note that this might work with simple arithmetic, but doesn't work with e.g. SUM or COUNT: you'll get reference '[colname]' not supported (reference to group function) – Jell

This also doesn't seem to work if the column name you're selecting shares the name with another actual column on any of the tables (from tables or joined tables) – Inspissate

Answer 3

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

Subcontraoctave

Is this better than just selecting total_sale and s.f1 (without the subquery), and then just loop through the results using PHP to perform the calculations for f1_percent1? I'm torn if I should follow the PHP approach or use this answer instead. – Pitts

Answer 4

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;

Mulberry

Answer 5

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.

Labellum

Answer 6

I had a look at various answers here and did a few experiments.

Specifically I am using MariaDB 10.1.

For a "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 are using some sort of aggregate function with an inner join you can't use this, but you can combine this approach with the inner join approach as follows (NB VAT = "sales tax"... and NB in financial data currency fields typically have 4 decimal places, I think it's historic...)

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) don't allow nesting in the FROM clause. However this is easily solved by making a first View which lists the InvoiceNo and Subtotal, and then making a second View which references the first. Performance-wise I have no idea at all about this sort of double-View arrangement.

Litigate

Answer 7

I have been testing the following and it seems to work all the time, maybe there is a reason for this, is it because I have predefined the variable @total_sales as being a value not a string, and have not redefined its type during the select statement ??

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

Cooker

The main problem with variables is not typing, but order of execution. The doc states that "other than in SET statements, 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 order of evaluation for expressions involving user variables is undefined.". In your example, @total_sales may have the right value when calculating f1_percent, but it may also have the value of the preceding row. – Palikar