Calculating percentages per product using aggregate functions and group by-CodePudding

I have a table with 5 rows representing two different products. The columns include the product, sales, discount. I'm trying to calculate the percentage of sales per product that included a discount. The table looks like this:

product	sales	discount
1	10	0
1	10	5
2	20	10
2	20	0
2	20	10

My results should look like the below (which I know because I've calculated this in Excel):

product	perc_discount
1	50.00
2	66.67

For each of the two products we are calculating the count of sales with discount divided by the total count of sales, so for product 1 it would be (1/2)*100 = 50.

My SQL code looks like the below:

SELECT
    product,
    (SELECT COUNT(*) FROM sales WHERE discount >0)/COUNT(*)*100 AS perc_discount
FROM sales
GROUP BY product

However, the result I'm getting is:

product	perc_discount
1	150.0
2	100.0

It seems to be calculating the total count of discounted sales in the table and diving it by the count of each product and I can't seem to figure out how to change it. Any ideas on how I can improve this?

Thanks.

CodePudding user response：

How about conditional sum?

SQL> select product,
  2         round(sum(case when discount > 0 then 1 else 0 end) / count(*) * 100, 2) perc_discount
  3  from sales
  4  group by product;

   PRODUCT PERC_DISCOUNT
---------- -------------
         1            50
         2         66,67

SQL>

So: add 1 for every discount row per product. Divide that sum with total number of rows per product (that's count). Round the result to 2 decimals (so that it looks prettier).

CodePudding user response：

You can use conditional aggregation. For example:

select
  product,
  100.0 * count(case when discount <> 0 then 'x' end) / count(*) as perc_discount
from sales
group by product