Alex Hankel Alex Hankel - 2 years ago 804
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
, 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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download