Red Devil Red Devil - 5 months ago 22
SQL Question

Need to calculate percentage

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


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 | 30.57% | 48


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.

Answer

Join the table to a query that calculates the sum of weight:

select t.name, size, 100.0 * weight / total, weight
from tableA t
join (select name, sum(weight) total
      from tableA
      group by name) s on s.name = t.name

This syntax will work on all relational databases (that I know).