CODEWITHSUNDEEP
phpmysql
Derek
Derek

Reputation: 57

MySQL - reduce the length of nested formulas in select statement

How do I reduce the length of nested formulas by using references for previous formulas?

For example I would like to do the following but get an error:

(orders.size_0 + orders.size_1 + orders.size_2 + orders.size_3 + orders.size_4 +     orders.size_5 + orders.size_6) AS tot_qty,
ROUND(tot_qty*orders.order_price,0) AS tot_price,
ROUND(tot_price/f.rate,0) AS price_gbp

Thanks, Derek.

Upvotes: 0

Views: 58

Answers (1)

Devart
Devart

Reputation: 122002

You may use a subqueries, for example -

SELECT tot_qty, tot_price, ROUND(tot_price / sub1.rate,0) AS price_gbp FROM (
  SELECT tot_qty, ROUND(tot_qty * sub2.order_price, 0) AS tot_price FROM (
    SELECT (orders.size_0 + orders.size_1 + orders.size_2 + orders.size_3 + orders.size_4 + orders.size_5 + orders.size_6) AS tot_qty FROM table
      ...
    ) sub2
  ) sub1

Upvotes: 1

Related Questions