CODEWITHSUNDEEP
sqloracle-databasedecimalrounding
Valeria Izquierdo
Valeria Izquierdo

Reputation: 1

SQL - 1. Round the difference to 2 decimal places

I am trying to create an SQL statement with a subquery in the SELECT attribute list to show the product id, the current price and the difference between the current price and the overall average.

I know that using the ROUND function will round the difference to zero decimals but I want to round the difference to 2 decimal places. 

SELECT p_code, p_price, ROUND(p_price - (SELECT AVG(p_price) FROM product)) AS "Difference" 
FROM product;

I tried using CAST but it still gave me the same output.

SELECT p_code, p_price, CAST(ROUND(p_price - (SELECT AVG(p_price) FROM Lab6_Product)) as numeric(10,2)) AS "Difference" 
FROM lab6_product;

Thank you in advance for your time and help!

Upvotes: 0

Views: 2312

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269447

round() takes a second argument:

SELECT p_code, p_price,
       ROUND(p_price - AVG(p_price) OVER (), 2) AS "Difference"
FROM product;

Note that I also changed the subquery to a window function.

I often recommend converting to a number or decimal/numeric) instead:

SELECT p_code, p_price,
       cast(p_price - AVG(p_price) OVER () as number(10, 2)) AS "Difference"
FROM product;

This ensures that the two decimal points are displayed as well.

Upvotes: 1

Related Questions