GROUP BY SQL Server 后占总 SUM 的百分比
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/46909494/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Percentage from Total SUM after GROUP BY SQL Server
提问by Ryan Gadsdon
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:
例如：总和是 300，因此我需要这样的结果：
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
任何帮助将不胜感激 - SQL fiddle http://sqlfiddle.com/#!9/80f91/2
回答by Gordon Linoff
You don't need a cross join. Just use window functions:
你不需要一个cross join. 只需使用窗口函数：
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 JOINfor this query:
请注意，JOIN此查询不需要：
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.
SQL Server 进行整数除法。我使用十进制数进行此类计算，因此它们更有意义。
Hereis a SQL Fiddle, with two changes:
这是一个 SQL Fiddle，有两个变化：
- The database is changed to SQL Server.
- The total is stored as a number rather than a string.
- 数据库更改为 SQL Server。
- 总数存储为数字而不是字符串。
回答by Gowtham Alan
You can use Common table Expression...
您可以使用公用表表达式...
;with cte as(
select P.PersonID,SUM(cast(PA.Total as int))Total from Package PA join Person P on P.PersonID = PA.PackageFK GROUP BY P.PersonID
)
select PersonId,Total,cast (Total * 100 / t.GrandTotal as varchar) + '%' [Percentage] from cte cross join(select SUM(cast(Total as int)) as GrandTotal from Package)t
回答by KnarfaLingus
Like the others said, you can use CTE's to solve the issue, mine is a little different in that I didn't use a CROSS JOIN (no special reason) and did a double CTE. Also I threw a NULLIF in to prevent possible divide by zero errors (will result in NULL instead).
就像其他人说的那样，您可以使用 CTE 来解决问题，我的有点不同，因为我没有使用 CROSS JOIN（没有特殊原因）并使用了双 CTE。我还抛出了一个 NULLIF 以防止可能的除以零错误（将导致 NULL 代替）。
Also, the Package table Total column should be a numeric type (not the specific "numeric" type but a column type that can hold numbers), any time you are summing or performing math on a column, thats a tip-off it should be numeric.
此外，Package table Total 列应该是数字类型（不是特定的“数字”类型，而是可以容纳数字的列类型），任何时候对列求和或执行数学运算，这都应该是一个提示数字。
;WITH PersonTotals AS
(
SELECT P.PersonID, SUM(CAST(PA.Total AS MONEY)) Total
FROM Person P
JOIN Package PA ON P.PersonID = PA.PackageFK
GROUP BY P.PersonID
),
GrandTotal AS
(
SELECT SUM(PT.Total) Total
FROM PersonTotals PT
)
SELECT PT.*, (PT.Total / NULLIF((SELECT Total From GrandTotal),0)) * 100 Percentage
FROM PersonTotals PT
回答by Sreenu131
Try this below code
试试下面的代码
;With cte(PersonID ,Total)
AS
(
SELECT 1 ,75 UNION ALL
SELECT 2 ,75 UNION ALL
SELECT 3 ,15 UNION ALL
SELECT 4 ,15 UNION ALL
SELECT 5 ,60 UNION ALL
SELECT 6 ,60
)
SELECT PersonID,
Total,
CAST(CAST((MAX(total)OVER(partition BY personid ORDER BY total)*100.0/
MaxSum) AS INT)AS VARCHAR(5))+ '%' AS Percentage
FROM (SELECT personid,
total,
stotal,
Max(stotal)
OVER(
ORDER BY stotal DESC) AS MaxSum
FROM (SELECT personid,
total,
Sum(total)
OVER(
ORDER BY personid) AS STotal
FROM Cte)dt
GROUP BY dt.personid,
dt.total,
dt.stotal)dt2
ORDER BY dt2.personid ASC
Result
结果
PersonID Total Percentage
------------------------------
1 75 25%
2 75 25%
3 15 5%
4 15 5%
5 60 20%
6 60 20%
