CODEWITHSUNDEEP
c#sql-server
Miranda Code
Miranda Code

Reputation: 65

Sum Query with same ID values no repeating

SELECT s.[Statistics ID], s.[Player ID], s.[Game ID], s.[Season ID], s.[Goals Scored], s.[Assists Made], s.[Key Passes], s.[Successful Tackles], s.[Successful Interceptions], s.[Goals Conceded], p.[Player Name], p.[Player ID]
FROM Statistics$ s, Players$ p WHERE s.[Player ID]=p.[Player ID]

This is what shows up on the preview

Hey guys, so I'm having kind of a problem with this query, but what I want to happen is for all the values like Goals Scored, Assists Made,Key passes etc to sum up for each Player ID making them appear only once.

Upvotes: 1

Views: 139

Answers (1)

Justin Pihony
Justin Pihony

Reputation: 67085

You need to use aggregates such as GROUP BY and then SUM(...)

SELECT DISTINCT s.[Statistics ID], s.[Player ID], s.[Game ID], s.[Season ID], 
    p.[Player Name], Stats.*
FROM Statistics$ s
    JOIN Players$ p ON s.[Player ID] = p.[Player ID]
    JOIN (
        SELECT s.[Player ID], SUM(s.[Goals Scored]) AS GoalsScored, 
            SUM(s.[Assists Made]) AS AssistsMade, SUM(s.[Key Passes]) AS KeyPasses, 
            SUM(s.[Successful Tackles]) AS SuccessfulTackles, 
            SUM(s.[Successful Interceptions]) AS SuccessfulInterceptions, 
            SUM(s.[Goals Conceded]) AS GoalsConceded
        FROM Statistics$ s
        GROUP BY s.[Player ID]
    ) AS Stats ON Stats.[Player ID] = s.[Player ID]

Note that you have to use an aggregate function for any column that isn't in the GROUP BY, but you can join the results together with the player details as I have done above

Upvotes: 3

Related Questions