Alex Hankel Alex Hankel - 7 months ago 290
SQL Question

Trying to round a calculation to 2 decimal place in a new column in mySQL

So I am trying to...


Write a SELECT statement that returns these column names and data from the Products table:


  • product_name

  • list_price

  • discount_percent

  • discount_amount


    • A column that’s calculated from the previous two columns


  • discount_price


    • A column that’s calculated from the previous three columns




Round the discount_amount and discount_price columns to 2 decimal places.

Sort the result set by discount price in descending sequence.

Use the LIMIT clause so the result set contains only the first 5 rows.


So far I have

USE my_guitar_shop;

SELECT product_name, list_price, discount_percent,
CONCAT(discount_percent / 100 * list_price)
AS discount_amount
FROM products


I am able to return
discount_amount
, but not able to be able to round the column to 2 decimal places.

How would I also go about returning a second column? Such as

ROUND(list_price - discount_amount, 2)
AS discount_price


It says it doesn't recognize discount_amount?

Answer

Use ROUND() instead of CONCAT.

ROUND(discount_percent / 100 * list_price, 2)

The first argument is the number to round. The second argument is how many decimal places to round to.

http://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html#function_round