如何计算T-SQL中的GROUP BY行

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来创建一个名为“Games playing”的新列。例：

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

2 个解决方案

#1

If you have repeating Game_id's and you'd like to count the distinct values, you can add a

如果你有重复的Game_id，并且你想计算不同的值，你可以添加一个

COUNT (DISTINCT Game_id)

clause to your SELECT statement.

SELECT语句的子句。

#2

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

#1