Reputation: 13
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
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