如何计算 T-SQL 中的 GROUP BY 行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8548534/
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
How to count GROUP BY rows in T-SQL
提问by JF Beaulieu
I have this SQL query that does a GROUP BY to merge together all rows that contain the same Player_id but not the same Game_id:
我有这个 SQL 查询，它执行 GROUP BY 以将包含相同 Player_id 但不相同 Game_id 的所有行合并在一起：
SELECT p.Player_id,
p.Name,
p.Position,
SUM(s.Goals) AS goalsb,
SUM(s.Assists) AS assistsb,
SUM(s.Points) AS pointsb
FROM Dim_Player AS p
INNER JOIN Fact_Statistics AS s ON s.Player_id = p.Player_id
GROUP BY p.Player_id, p.Name, p.Position
ORDER BY pointsb DESC, goalsb DESC
What I want to do is implant a COUNT each time the GROUP BY merges a row with another to create a new column called "Games played". Example:
我想要做的是每次 GROUP BY 将一行与另一行合并以创建一个名为“已玩游戏”的新列时植入一个 COUNT。例子：
Player_id Game_id goalsb
8470598 465 1
8470598 435 1
this will be grouped together with the SQL query above to become:
这将与上面的 SQL 查询组合在一起成为：
Player_id goalsb
8470598 2
But I want to have this:
但我想要这个：
Player_id goalsb Games_played
8470598 2 2
回答by Lieven Keersmaekers
If you have repeating Game_id's and you'd like to count the distinct values, you can add a
如果您有重复的Game_id's 并且您想计算不同的值，您可以添加一个
COUNT (DISTINCT Game_id)
clause to your SELECTstatement.
你的SELECT陈述的条款。
回答by Russell Hart
Add a count function to the select query. COUNT(*) counts each row in the group, independent of the columns selected.
向选择查询添加计数函数。COUNT(*) 对组中的每一行进行计数，与所选的列无关。
SELECT p.Player_id, p.Name, p.Position, SUM(s.Goals) AS goalsb, SUM(s.Assists) AS assistsb, SUM(s.Points) AS pointsb, COUNT(*) as [Games_played]
FROM Dim_Player AS p INNER JOIN Fact_Statistics AS s ON s.Player_id = p.Player_id
GROUP BY p.Player_id, p.Name, p.Position, s.Game_id
ORDER BY pointsb DESC, goalsb DESC
