Red Devil Red Devil - 5 months ago 9
SQL Question

Percentage calculation when size is same but weight is different

I want to get the

weight
as a percentage of the total
weight
for each person.

Here's my data:

TableA

Name | Size | Weight
------------------------------------
Jamie | 0.25 | 48
Jamie | 0.50 | 48
Taylor | 0.25 | 55
Taylor | 0.30 | 54
Taylor | 0.45 | 48
Taylor | 0.45 | 60


And here's the output I would like:

Name | Size | Percentage | Weight
---------------------------------------------------------------
Jamie | 0.25 | 50% | 48
Jamie | 0.50 | 50% | 48
Taylor | 0.25 | 35.03% | 55
Taylor | 0.30 | 34.39% | 54
Taylor | 0.45 | 49.76% | 108


Eg the total weight for Jamie is 96 (48+48), so I have to calculate his weight values as a percentage of 96 for each row.
And for taylor there is size 0.45 twice but with different weight so in the output it should sum the weight (48+60) and than (108/217)*100=49.76 as percentage

Answer

I suggest that you need to summarize the source records first, then calculate the percentage. So, using a derived table I propose you sum the weight by name/size, and finish in the outer query.

SELECT
      name
    , size
    , 100 * Weight / SUM(Weight) OVER (PARTITION BY Name) AS Per
FROM (
      SELECT
            Name
          , Size
          , SUM(weight) AS weight
      FROM worker
      GROUP BY
            name
          , size
) AS w
Comments