ABSimon ABSimon - 4 months ago 9
SQL Question

How to shorten this MySQL Query (colums use often the same sql parts...)

I am not an MySQL Professinal, but my query works fine

SELECT r.id,

/* Total repurchase price per Ton
* (Quantitiy/t * repurchase price/dry) + Extra Costs
*/
(
SUM((rc.menge / 1000) * (rw.preis / (IF (rw.is_zement = 1, 1.25 , 1)) / (1 - (IFNULL(rw.h2o, 0) / 100)))) +
SUM((rc.menge / 1000) * pr.preis) +
IF ((r.extra_1_type > 0), IF ((r.extra_1_type = 1), extra_1 , extra_1 * SUM(rc.menge / 1000)) , 0) +
IF ((r.extra_2_type > 0), IF ((r.extra_2_type = 1), extra_2 , extra_2 * SUM(rc.menge / 1000)) , 0) +
IF ((r.finanzierung_satz > 0) AND (r.finanzierung_monate > 0) AND SUM(rc.menge / 1000),
((r.finanzierung_satz / 100) / 12) * r.finanzierung_monate * (
SUM((rc.menge / 1000) * (rw.preis / (IF (rw.is_zement = 1, 1.25 , 1)) / (1 - (IFNULL(rw.h2o, 0) / 100)))) +
SUM((rc.menge / 1000) * pr.preis) +
IF ((r.extra_1_type > 0), IF ((r.extra_1_type = 1), extra_1 , extra_1 * SUM(rc.menge / 1000)) , 0) +
IF ((r.extra_2_type > 0), IF ((r.extra_2_type = 1), extra_2 , extra_2 * SUM(rc.menge / 1000)) , 0)
)
, 0) ) as repurchase_price,

/* Profit
* (Quantitiy/t * Price) - repurchase_price
*/
(SUM(rc.menge) / 1000 * r.vk) - (

SUM((rc.menge / 1000) * (rw.preis / (IF (rw.is_zement = 1, 1.25 , 1)) / (1 - (IFNULL(rw.h2o, 0) / 100)))) +
SUM((rc.menge / 1000) * pr.preis) +
IF ((r.extra_1_type > 0), IF ((r.extra_1_type = 1), extra_1 , extra_1 * SUM(rc.menge / 1000)) , 0) +
IF ((r.extra_2_type > 0), IF ((r.extra_2_type = 1), extra_2 , extra_2 * SUM(rc.menge / 1000)) , 0) +
IF ((r.finanzierung_satz > 0) AND (r.finanzierung_monate > 0) AND SUM(rc.menge / 1000),
((r.finanzierung_satz / 100) / 12) * r.finanzierung_monate * (
SUM((rc.menge / 1000) * (rw.preis / (IF (rw.is_zement = 1, 1.25 , 1)) / (1 - (IFNULL(rw.h2o, 0) / 100)))) +
SUM((rc.menge / 1000) * pr.preis) +
IF ((r.extra_1_type > 0), IF ((r.extra_1_type = 1), extra_1 , extra_1 * SUM(rc.menge / 1000)) , 0) +
IF ((r.extra_2_type > 0), IF ((r.extra_2_type = 1), extra_2 , extra_2 * SUM(rc.menge / 1000)) , 0)
)
, 0)

) as profit,

/* Profit Percentage
* Profit / (repurchase_price / 100)
*/
((SUM(rc.menge) / 1000 * r.vk) - (

SUM((rc.menge / 1000) * (rw.preis / (IF (rw.is_zement = 1, 1.25 , 1)) / (1 - (IFNULL(rw.h2o, 0) / 100)))) +
SUM((rc.menge / 1000) * pr.preis) +
IF ((r.extra_1_type > 0), IF ((r.extra_1_type = 1), extra_1 , extra_1 * SUM(rc.menge / 1000)) , 0) +
IF ((r.extra_2_type > 0), IF ((r.extra_2_type = 1), extra_2 , extra_2 * SUM(rc.menge / 1000)) , 0) +
IF ((r.finanzierung_satz > 0) AND (r.finanzierung_monate > 0) AND SUM(rc.menge / 1000),
((r.finanzierung_satz / 100) / 12) * r.finanzierung_monate * (
SUM((rc.menge / 1000) * (rw.preis / (IF (rw.is_zement = 1, 1.25 , 1)) / (1 - (IFNULL(rw.h2o, 0) / 100)))) +
SUM((rc.menge / 1000) * pr.preis) +
IF ((r.extra_1_type > 0), IF ((r.extra_1_type = 1), extra_1 , extra_1 * SUM(rc.menge / 1000)) , 0) +
IF ((r.extra_2_type > 0), IF ((r.extra_2_type = 1), extra_2 , extra_2 * SUM(rc.menge / 1000)) , 0)
)
, 0)

))

/

((

SUM((rc.menge / 1000) * (rw.preis / (IF (rw.is_zement = 1, 1.25 , 1)) / (1 - (IFNULL(rw.h2o, 0) / 100)))) +
SUM((rc.menge / 1000) * pr.preis) +
IF ((r.extra_1_type > 0), IF ((r.extra_1_type = 1), extra_1 , extra_1 * SUM(rc.menge / 1000)) , 0) +
IF ((r.extra_2_type > 0), IF ((r.extra_2_type = 1), extra_2 , extra_2 * SUM(rc.menge / 1000)) , 0) +
IF ((r.finanzierung_satz > 0) AND (r.finanzierung_monate > 0) AND SUM(rc.menge / 1000),
((r.finanzierung_satz / 100) / 12) * r.finanzierung_monate * (
SUM((rc.menge / 1000) * (rw.preis / (IF (rw.is_zement = 1, 1.25 , 1)) / (1 - (IFNULL(rw.h2o, 0) / 100)))) +
SUM((rc.menge / 1000) * pr.preis) +
IF ((r.extra_1_type > 0), IF ((r.extra_1_type = 1), extra_1 , extra_1 * SUM(rc.menge / 1000)) , 0) +
IF ((r.extra_2_type > 0), IF ((r.extra_2_type = 1), extra_2 , extra_2 * SUM(rc.menge / 1000)) , 0)
)
, 0)
) / 100)

as profit_percentage,

FROM recipe as r
LEFT JOIN recipecomponent as rc ON r.id = rc.recipe_id
LEFT JOIN rawmaterial as rw ON rc.rawmaterial_id = rw.id
LEFT JOIN press as pr ON r.press_id = pr.id
GROUP BY r.id
ORDER BY lieferdatum desc


As you can see, to calculate price, profit, profit percentage, I use often
the same parts... Is it possible to shorten my SQL Query? To make it more
'elegant' :)

Thank you & Best Regars Simon

Answer

As a generic answer, if you repeat caculations in your query, you can use derived tables to avoid repetition:

select d.a, d.b, d.a*d.b
from (
  select x+y+z as a, d+e+f as b
  from yourtable
) as d;
Comments