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
解决方案
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:
The database is changed to SQL Server.
The total is stored as a number rather than a string.