如何计算T-SQL中的GROUP BY行 [英] How to count GROUP BY rows in T-SQL

问题描述

我有一个SQL查询，执行一个GROUP BY合并所有包含相同Player_id但不是相同Game_id的行：

  SELECT p.Player_id，
 p.Name，
 p.Position，
 SUM（s.Goals）AS goalsb，
 SUM（sAssists）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 
  

我想要做的是植入一个COUNT每次GROUP BY合并一行与另一个创建一个新的列称为游戏。示例：

  Player_id Game_id goalsb 
 8470598 465 1 
 8470598 435 1 
  

这将与上面的SQL查询一起分组：

  Player_id goalsb 
 8470598 2 
  

但我想有这个：

  Player_id goalsb Games_played 
 8470598 2 2 
  pre> 
 
解决方案
如果您重复 Game_id 喜欢计算不同值，您可以添加
  COUNT（DISTINCT Game_id）
  
子句添加到 SELECT 语句中。
 
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:
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:
Player_id      Game_id    goalsb
8470598        465        1
8470598        435        1
this will be grouped together with the SQL query above to become:
Player_id          goalsb
8470598            2
But I want to have this:
Player_id          goalsb       Games_played
8470598            2            2

解决方案
If you have repeating Game_id's and you'd like to count the distinct values, you can add a  
COUNT (DISTINCT Game_id)
clause to your SELECT statement.

                        
这篇关于如何计算T-SQL中的GROUP BY行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！