How to count GROUP BY rows in T-SQL
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.
JF Beaulieu
Full Stack C# .NET / Typescript / Javascript Web Developer Microsoft MCSD Web Applications certified developer Interested in modern web technologies such as: .NET Framework 4.7.2 C# 8.0 ASP.NET Core 3.1 ASP.NET MVC 5 Entity Framework 7 SQL Server 2016 RESTful Web Services WCF Services (SoA) jQuery React Angular AngularJS Bootstrap SignalR
Updated on July 05, 2022Comments
-
JF Beaulieu almost 2 years
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