4

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?

Improve this question
6
  • What is this query supposed to do? In particular, what do you mean by using HAVING without GROUP BY?
    – Andriy M
    Nov 3, 2013 at 20:19
  • Did you mean WHERE instead of HAVING? The query should work well with WHERE.
    – Shlomi Noach
    Nov 4, 2013 at 5:47
  • 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/….
    – ivodvb
    Nov 4, 2013 at 7:51
  • 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.
    – Andriy M
    Nov 4, 2013 at 9:53
  • 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 :)
    – ivodvb
    Nov 5, 2013 at 14:41

2 Answers 2

Reset to default
2

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
;
Improve this answer
1
  • Many thanks! This worked out for me and I think this is the best way to do it,
    – ivodvb
    Nov 14, 2013 at 11:15
1

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
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.