mysql group by 比例,GROUP BY SQL Server之后的总SUM的百分比

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.