Search code examples
mysqlwindow

MySQL Window function calculates percentage

Hi I used MySQL Window function to calculate each product's profit percentage over total profit. However, it doesn't work as I expected.

The data: Table A

total   Profit      productCode productName
5554.56 S10_1949    1952        Alpine Renault 1300
5473.71 S12_4675    1969        Dodge Charger
5078.35 S12_1108    2001        Ferrari Enzo
4475    S12_1099    1968        Ford Mustang

My code

select A.totalProfit, 
A.totalProfit/sum(A.totalProfit) over (PARTITION BY A.totalProfit) as profitPercent, 
A.productCode, A.productName
from A;

The output I got, which profitPercent was not calculated correctly

totalProfit profitPercent   productCode productName
5554.56     1               S10_1949    1952 Alpine Renault 1300
5473.71     1               S12_4675    1969 Dodge Charger
5078.35     1               S12_1108    2001 Ferrari Enzo
4475        1               S12_1099    1968 Ford Mustang

Expected output

totalProfit profitPercent   productCode productName
5554.56     0.26987963      S10_1949    1952 Alpine Renault 1300
5473.71     0.265951368     S12_4675    1969 Dodge Charger
5078.35     0.246741996     S12_1108    2001 Ferrari Enzo
4475        0.217427005     S12_1099    1968 Ford Mustang

Any suggestion about my code ? thanks

Solution

  • Drop the PARTITION from your window function:

    SELECT
    A.totalProfit, 
    A.totalProfit / SUM(A.totalProfit) OVER () AS profitPercent,
    A.productCode,
    A.productName
FROM A;

    enter image description here

    Demo

    In this case, you want to take the sum of the profit over the entire table, so there should be no partition in your call to SUM(). It is perfectly legal for OVER to have no input, and in that case it just means no partition, which defaults to include the entire table.