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

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
``````