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:
- Repeat the calculations like in the first code above.
- 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?
