CODEWITHSUNDEEP
sqlsql-server
OddsMaker65
OddsMaker65

Reputation: 23

sql sum group by multiple columns

I'm trying to score gaming tournaments. There are multiple tournaments and each team has two players, player1 and player2. When signing up either player could be in player1 or Player2 position. I need to sum up the points earned across tournament whenever the same two player are teamed together.

Tournament_ID|Player1|Player2|Points
------------------------------------
1            |Smith  |Green  |8
1            |Brown  |Peck   |3
1            |Jones  |Healy  |2

2            |Smith  |Green  |5
2            |Peck   |Brown  |3
2            |Jones  |Healy  |1

3            |Healy  |Jones  |9
3            |Smith  |Green  |5
3            |Peck   |Brown  |3

Results should look like:

Place  Team              Total Points
1      Smith & Green        18
2      Jones & Healy        12
3      Brown & Peck          9

Upvotes: 2

Views: 56

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522824

Here is one way:

WITH cte AS (
    SELECT
        CASE WHEN Player1 < Player2 THEN Player1 ELSE Player2 END AS Player1,
        CASE WHEN Player1 < Player2 THEN Player2 ELSE Player1 END AS Player2,
        Points
    FROM yourTable
)

SELECT
    ROW_NUMBER() OVER (ORDER BY SUM(Points) DESC) Place,
    Player1 + ' & ' + Player2,
    SUM(Points) AS [Total Points]
FROM cte
GROUP BY
    Player1 + ' & ' + Player2;

enter image description here

Demo

Upvotes: 4

Related Questions