Reputation: 23
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
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;
Upvotes: 4