CODEWITHSUNDEEP
sqloracle-database
Kurt Lee
Kurt Lee

Reputation: 13

Calculating percent discount on Oracle SQL Developer

ive been trying to work out this problem for an hour or so and just cant figure it out. May someone point me to the right direction please? I am working with the data from OE schema off of Oracle SQL Developer.

I need to show the product category name for products that have the largest % discount off of the list_price.

This is my code:

SELECT    ct.category_name, ROUND((pi.list_price pi.min_price)*100/pi.list_Price) AS Percent_Discount
FROM        oe.product_information pi JOIN oe.categories_tab ct ON pi.category_id = ct.category_id
GROUP BY  ct.category_name, ROUND((pi.list_price-pi.min_price)*100/pi.list_Price)
ORDER BY  ROUND((pi.list_price-pi.min_price)*100/pi.list_Price) desc;

Here is an image of my results of the query

The results actually go all the way down to 150ish or so which includes every single product.

My question is: How do I get it so my percent number is two decimal places instead of it being rounded into a whole number? I understand I used the round statement, but I only did it because without it, the numbers are about 15 digits. Also, do you think my code is answering the problem correctly? I feel like i should only show the top 10 or 20 category names with the biggest discount. Is there anyway I could do that instead of listing all of them?

Upvotes: 0

Views: 1273

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

ROUND() takes a second argument, which is the number of decimal places you want (see here). So:

SELECT ct.category_name,
       MAX(ROUND((pi.list_price - pi.min_price)*100/pi.list_Price, 2)) AS Percent_Discount
FROM oe.product_information pi JOIN
     oe.categories_tab ct
     ON pi.category_id = ct.category_id
GROUP BY ct.category_name
ORDER BY 2 desc;

Note: If you want one row per category then category_name should be the only key in the GROUP BY.

Upvotes: 1

Related Questions