MySQL temporary column in SELECT statement

Question

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?

• Andriy M: What is this query supposed to do? In particular, what do you mean by using HAVING without GROUP BY?
• Shlomi Noach: Did you mean WHERE instead of HAVING? The query should work well with WHERE.
• ivodvb: This query is used to calculate sell prices. I'm using HAVING instead of WHERE because the temporary column sale_price is only available in HAVING clauses and not in WHERE clauses. See stackoverflow.com/questions/630815/… and stackoverflow.com/questions/1221256/….
• Andriy M: You seem to completely misunderstand the purpose of HAVING, and the indulgent nature of MySQL extensions to SQL doesn't help at all. Do you realise that using HAVING in this way you can only get one row out of the query? Did you mean the result to consist of just one row? I'm asking this because my answer, if I ever get round to writing one, would depend on that.
• ivodvb: At this moment, I'm able to get multiple rows out of this query and that is what I'd like to do, because there could be more than 1 products that match the requirement to cost more than 250ドル. Hope you manage to write an answer :)

Answer 1

Based on your current code, it appears that a product can have at most one match in either customer_price_increment or general_price_increment. With that fact in mind, I would probably try a different approach.

First, I would rewrite the base query returning all the prices like this:

SELECT
 p.id AS product_number,
 p.name AS product_name,
 p.redemption_price,
 COALESCE(p.redemption_price * cpi.increment_percentage / 100, 0) AS customer_price_increment,
 COALESCE(p.redemption_price * gpi.increment_percentage / 100, 0) AS general_price_increment,
 p.redemption_price * (1 + COALESCE(cpi.increment_percentage, gpi.increment_percentage, 0) / 100) AS sale_price
FROM product AS p
LEFT JOIN customer_price_increment AS cpi ON cpi.id = p.id AND cpi.customer_id = 4
LEFT JOIN general_price_increment AS gpi ON gpi.id = p.id

To filter on sale_price, I would just use the above query as a derived table, so that I could reference the sale_price alias and avoid repeating the entire expression in the WHERE clause:

SELECT *
FROM (
 SELECT
 p.id AS product_number,
 p.name AS product_name,
 p.redemption_price,
 COALESCE(p.redemption_price * cpi.increment_percentage / 100, 0) AS customer_price_increment,
 COALESCE(p.redemption_price * gpi.increment_percentage / 100, 0) AS general_price_increment,
 p.redemption_price * (1 + COALESCE(cpi.increment_percentage, gpi.increment_percentage, 0) / 100) AS sale_price
 FROM product AS p
 LEFT JOIN customer_price_increment AS cpi ON cpi.id = p.id AND cpi.customer_id = 4
 LEFT JOIN general_price_increment AS gpi ON gpi.id = p.id
) AS s
WHERE sale_price > 250
;

I believe you could also make the first query a view and then just select from it filtering the results as necessary:

SELECT *
FROM sale_prices_view
WHERE sale_price > 250
;

• ivodvb: Many thanks! This worked out for me and I think this is the best way to do it,

Answer 2

besides the question you have asked there are lot more potential issues with the query the way it is written. this query will not perform good with large data in tables. Try one of the below option. One without Nested Select and Second with Nested select. assuming you have right indexes on each table below query should be good and much more readable.

without nested select stmt

 SELECT
 /** Product details **/
 t1.product_id AS product_number
 ,t1.name AS product_name
 /** Price details **/
 ,t1.redemption_price AS redemption_price
 ,CASE WHEN t1.product_id=t2.product_id AND t2.customer_id=4 
 THEN ( (t2.increment_percentage/100) * t1.redemption_price)
 ELSE 0 
 END AS customer_price_increment
 ,CASE WHEN t1.product_id=t3.product_id 
 THEN ( (t3.increment_percentage/100) * t1.redemption_price)
 ELSE 0
 END AS general_price_increment
 ,CASE WHEN --customer_price_increment
 (CASE WHEN t1.product_id=t2.product_id AND t2.customer_id=4 
 THEN ( (t2.increment_percentage/100) * t1.redemption_price)
 ELSE 0 
 END)=0
 THEN 0
 ELSE --general_price_increment
 (CASE WHEN t1.product_id=t3.product_id 
 THEN ( (t3.increment_percentage/100) * t1.redemption_price)
 ELSE 0
 END)
 END AS sale_price
 FROM product t1
 FULL OUTER JOIN customer_price_increment t2
 ON t1.product_id=t2.product_id
 AND t2.customer_id=4
 FULL OUTER JOIN general_price_increment t3
 ON t1.product_id = t3.product_id
 WHERE 
 (CASE WHEN --customer_price_increment
 (CASE WHEN t1.product_id=t2.product_id AND t2.customer_id=4 
 THEN ( (t2.increment_percentage/100) * t1.redemption_price)
 ELSE 0 
 END)=0
 THEN 0
 ELSE --general_price_increment
 (CASE WHEN t1.product_id=t3.product_id 
 THEN ( (t3.increment_percentage/100) * t1.redemption_price)
 ELSE 0
 END)
 END) > 250

with nested select stmt

 SELECT 
 product_number
 ,product_name
 ,redemption_price
 ,customer_price_increment
 ,general_price_increment
 ,CASE WHEN customer_price_increment = 0 
 THEN general_price_increment 
 ELSE customer_price_increment 
 END AS sale_price
 FROM
 (
 SELECT
 /** Product details **/
 t1.product_id AS product_number
 ,t1.name AS product_name
 /** Price details **/
 ,t1.redemption_price AS redemption_price
 ,CASE WHEN t1.product_id=t2.product_id AND t2.customer_id=4 
 THEN ( (t2.increment_percentage/100) * t1.redemption_price)
 ELSE 0 
 END AS customer_price_increment
 ,CASE WHEN t1.product_id=t3.product_id 
 THEN ( (t3.increment_percentage/100) * t1.redemption_price)
 ELSE 0
 END AS general_price_increment
 ,CASE WHEN --customer_price_increment
 (CASE WHEN t1.product_id=t2.product_id AND t2.customer_id=4 
 THEN ( (t2.increment_percentage/100) * t1.redemption_price)
 ELSE 0 
 END)=0
 THEN 0
 ELSE --general_price_increment
 (CASE WHEN t1.product_id=t3.product_id 
 THEN ( (t3.increment_percentage/100) * t1.redemption_price)
 ELSE 0
 END)
 END AS sale_price
 FROM product t1
 FULL OUTER JOIN customer_price_increment t2
 ON t1.product_id=t2.product_id
 AND t2.customer_id=4
 FULL OUTER JOIN general_price_increment t3
 ON t1.product_id = t3.product_id
 )qry1
 WHERE 
 (CASE WHEN customer_price_increment = 0 
 THEN general_price_increment 
 ELSE customer_price_increment 
 END) > 250