在GROUP BY SQL Server之后占总SUM的百分比 [英] Percentage from Total SUM after GROUP BY SQL Server

问题描述

我得到这些结果:

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

使用如下表格:

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.

例如:总和为300，因此我需要这样的结果:

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.

任何帮助将不胜感激-SQL小提琴 http://sqlfiddle.com/#!9/80f91/2

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

推荐答案

您不需要cross join.只需使用窗口功能:

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;

请注意，此查询不需要JOIN:

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执行整数除法.我使用十进制数字进行此类计算，因此更有意义.

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

此处是一个SQL提琴，有两个更改:

Here is a SQL Fiddle, with two changes:

1. 数据库已更改为SQL Server.
2. 总数存储为数字而不是字符串.

这篇关于在GROUP BY SQL Server之后占总SUM的百分比的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！