Avoid same multiple calculations in a single query

Let’s say I have the following query in mysql:

SELECT net_price,
 net_price*tax AS price,
 net_price*tax*qty AS total,
 net_price*tax*qty*discount AS discount_total
 FROM product

In this sample only the first returned column is actual data from the table, the rest are values calculated on the fly. But as you can see each calculated field continues calculation from the previous field. So theoretically I might rewrite the query like this:

SELECT net_price,
 net_price*tax AS price,
 price*qty AS total,
 total*discount AS discount_total
 FROM product

except that this won’t work because there are no columns like price and total.

This is a simple example but sometimes I need to perform much more complex calculations and then use them as the basis for other calculated fields. So far I have used one of the two solutions:

  1. Repeat the calculations like in the first code above.
  2. Make several nested select queries (derived tables).

Both solutions are ugly to me because they require either repetition of code or nesting queries many levels deep - bad for readability and possibly on performance, too. Is there a better solution?

no :slight_smile:

That was not the answer I wanted to hear but thanks anyway :).

BTW, do you know if mysql optimizer is smart enough to notice there are multiple identical expressions and evaluate them only once for performance?

i’m sorry, i don’t