I'd like to do some calculations in a MySQL query. I've created some temporary columns which I'd like to use for the calculation.
SQL fiddle for currently working (but discouraged) SQL query: http://sqlfiddle.com/#!2/5dc99/14
According to the MySQL docs 9.4 User-Defined variables(http://dev.mysql.com/doc/refman/5.0/en/user-variables.html): "As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value within the same statement."
SELECT
/** Product details **/
product.id AS product_number,
product.name AS product_name,
/** Price details **/
@redemption_price := product.redemption_price AS redemption_price,
@customer_price_increment := (
CASE
(SELECT COUNT(1) FROM customer_price_increment AS cpi WHERE cpi.product_id = product.id AND cpi.customer_id = 4)
WHEN 0 THEN
0
ELSE
@redemption_price * (SELECT cpi.increment_percentage / 100 FROM customer_price_increment AS cpi WHERE cpi.customer_id = 4 AND cpi.product_id = product.id)
END
) AS customer_price_increment,
@general_price_increment := (
CASE
(SELECT COUNT(1) FROM general_price_increment AS gpi WHERE gpi.product_id = product.id)
WHEN 0 THEN
0
ELSE
@redemption_price * (SELECT gpi.increment_percentage / 100 FROM general_price_increment AS gpi WHERE gpi.product_id = product.id)
END
) AS general_price_increment,
@sale_price := @redemption_price + (
CASE
@customer_price_increment
WHEN 0 THEN
@general_price_increment
ELSE
@customer_price_increment
END
) AS sale_price
FROM
product
I'd like to add HAVING sale_price > 250 to only show products with a product price higher than $250.
Adding HAVING sale_price > 250 doesn't work, because of the usage of the user defined variables (see http://sqlfiddle.com/#!2/5dc99/19).
I've removed the user defined variables as much as I could, but I'm not getting it to work without all the user defined variables. http://sqlfiddle.com/#!2/5dc99/24 shows results, because he HAVING clause isin't there. http://sqlfiddle.com/#!2/5dc99/23 doesn't show any results while it should, because there are products that costs more than $250.
Simply removing all the user defined variables doesn't solve the problem, because the temporary columns cannot be reused (http://sqlfiddle.com/#!2/5dc99/25). Unknown column 'general_price_increment' in 'field list'
.
SELECT
/** Product details **/
product.id AS product_number,
product.name AS product_name,
/** Price details **/
product.redemption_price AS redemption_price,
product.redemption_price * (
CASE
(SELECT COUNT(1) FROM customer_price_increment AS cpi WHERE cpi.product_id = product.id AND cpi.customer_id = 4)
WHEN 0 THEN
0
ELSE
(SELECT cpi.increment_percentage / 100 FROM customer_price_increment AS cpi WHERE cpi.customer_id = 4 AND cpi.product_id = product.id)
END
) AS customer_price_increment,
product.redemption_price * (
CASE
(SELECT COUNT(1) FROM general_price_increment AS gpi WHERE gpi.product_id = product.id)
WHEN 0 THEN
0
ELSE
(SELECT gpi.increment_percentage / 100 FROM general_price_increment AS gpi WHERE gpi.product_id = product.id)
END
) AS general_price_increment,
redemption_price + (
CASE
customer_price_increment
WHEN 0 THEN
general_price_increment
ELSE
customer_price_increment
END
) AS sale_price
FROM
product
HAVING
sale_price > 250
What can I do to make this work?
WHERE
instead ofHAVING
? The query should work well withWHERE
.