oncik oncik - 1 month ago 8
SQL Question

How to round the newly calculated value?

SELECT product_c, description, cost,
CASE WHEN product_no < 9 THEN cost * 1.10
WHEN product_no BETWEEN 10 AND 15 THEN cost * 1.20
WHEN product_no BETWEEN 15 AND 20 THEN cost * 1.25
WHEN product_no > 20 THEN cost * 1.30
END AS "New Cost"
FROM table
WHERE "New Cost" > 19


I don't know where to place
ROUND()
so that it can round my "New Cost" column to have nearest hundred or 2 decimal like 100.53.
How do I use alias column to sort out products that are greater than 19?

Answer

Put the entire case inside the function call. I would use cast() instead:

SELECT product_c, description, cost,    
       CAST((CASE WHEN product_no < 9 THEN cost * 1.10
                  WHEN product_no BETWEEN 10 AND 15 THEN cost * 1.20
                  WHEN product_no BETWEEN 15 AND 20 THEN cost * 1.25
                  WHEN product_no > 20 THEN cost * 1.30
             END) as DECIMAL(10, 2)) AS "New Cost"
FROM table
WHERE "New Cost" > 19

This is the same idea as round(), but it uses only the appropriate number of decimal places to store the result.