CODEWITHSUNDEEP
sql
K. Rawls
K. Rawls

Reputation: 115

SQL Calculate percentages of column values based on name

I have a SQL query that goes like this:

    SELECT name, SUM(weight) weight, product FROM table GROUP BY name, product;

Which gives me output like this:

    |name         weight     product  |
    |custA        100        trash    |
    |custA        300        recycling|
    |custB        50         trash    |
    |custB        450        recycling|

Now I want to calculate the percentage of weight based on the name but still separated by product. So instead the output would look like this:

    |name         weight     product  |
    |custA        .25        trash    |
    |custA        .75        recycling|
    |custB        .10        trash    |
    |custB        .90        recycling|

Is there any way I can accomplish this with a regular SQL query?

Upvotes: 1

Views: 4200

Answers (4)

scube
scube

Reputation: 28

Using CTE and correlated subquery

WITH TotalWeight(name, product, weightsum) AS 
(
    SELECT name, product, sum([weight]) as weightsum
    FROM custdetails
    GROUP BY name, product
)

SELECT name, product, weightsum, (0.0+weightsum)/(select sum([weight]) from custdetails where name = T.name) as percentage
FROM TotalWeight T;

This gives the following result:

name    product     weightsum  percentage
custA   recycling   300        0.750000000000
custA   trash       100        0.250000000000
custB   recycling   450        0.900000000000
custB   trash        50        0.100000000000

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270483

You can do this using window functions:

SELECT name, product, SUM(weight) as weight,
       SUM(weight) / SUM(SUM(weight)) OVER (PARTITION BY name) as ratio
FROM table
GROUP BY name, product;

Note that some databases do integer division. So, if weight is an integer, you may need to convert to a decimal of some type:

SELECT name, product, SUM(weight) as weight,
       SUM(weight) / SUM(1.0*SUM(weight)) OVER (PARTITION BY name) as ratio
FROM table
GROUP BY name, product;

Upvotes: 0

Abdullah Dibas
Abdullah Dibas

Reputation: 1507

Try this:

SELECT table.name, SUM(weight) / custWeightTable.custWeght as weight ,        product FROM table ,  (SELECT name, SUM (weight) as custWeight  FROM table GROUP BY name) custWeightTable WHERE table.name = custWeightTable.name GROUP BY table.name, table.product;

Upvotes: 0

Iłya Bursov
Iłya Bursov

Reputation: 24156

try something like this:

select a.name, a.weight/b.weight, a.product
from (
    select name, sum(weight) as weight, product
    from table
    group by name, product
) as a
inner join (
    select name, sum(weight) as weight
    from table
    group by name
) as b
on (a.name = b.name)

Upvotes: 1

Related Questions