如何在 T-SQL 中计算 GROUP BY 行数

发布于 2024-12-21 20:56:57 字数 818 浏览 1 评论 0原文

我有一个 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

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

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

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

慢慢从新开始 2024-12-28 20:56:58

如果您有重复的 Game_id，并且想要对不同值进行计数，则可以

COUNT (DISTINCT Game_id)

在 SELECT 语句中添加一个子句。

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.

回复收藏 0 原文

梨涡 2024-12-28 20:56:58

将计数函数添加到选择查询中。 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

Add a count function to the select query. COUNT(*) counts each row in the group, independent of the columns selected.

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

回复收藏 0 原文

~没有更多了~