Percentage from Total SUM after GROUP BY SQL Server

Question

I have these results:

PersonID    SUM(PA.Total) -------------------------    1            75    2            75    3            15    4            15    5            60    6            60 

With the table like:

PersonID    Total ------------------    1         50    2         50    3         10    4         10    5         40    6         40    1         25    2         25    3          5    4          5    5         20    6         20 

These are grouped by the person. Now I'm looking to add a column with the percentages for each person calculated from the total of all of their sums.

For example: the total sum is 300, and hence I need a result like this:

PersonID    SUM(PA.Total)   Percentage --------------------------------------    1            75              25%    2            75              25%    3            15              5%    4            15              5%    5            60              20%    6            60              20% 

I have looked at code online and I have come up with a fix such as this:

 SELECT       P.PersonID, SUM(PA.Total)      SUM(PA.Total) * 100 / [p] AS 'Percentage'  FROM       Person P  JOIN       Package PA ON P.PersonID = PA.PackageFK  CROSS JOIN       (SELECT SUM(PA.[Total]) AS [p]        FROM Package PA) t  GROUP BY       P.PersonID 

But I'm unsure how to incorporate the cross join into the join as well as the already group/sum section. Or whether this is along the right lines altogether.

Any help would be appreciated - SQL fiddle http://sqlfiddle.com/#!9/80f91/2

Answer 1

You don't need a cross join. Just use window functions:

SELECT P.PersonID, SUM(PA.Total),        SUM(PA.Total) * 100.0 / SUM(SUM(PA.Total)) OVER () AS Percentage FROM Person P JOIN      Package PA      ON P.PersonID = PA.PackageFK GROUP BY P.PersonID; 

Note that you do not need the JOIN for this query:

SELECT PA.PersonID, SUM(PA.Total),        SUM(PA.Total) * 100.0 / SUM(SUM(PA.Total)) OVER () AS Percentage FROM Package PA GROUP BY PA.PersonID; 

SQL Server does integer division. I do such calculations using decimal numbers so they make more sense.

Here is a SQL Fiddle, with two changes:

1. The database is changed to SQL Server.
2. The total is stored as a number rather than a string.