I have this table which stores all the game tuples within a chess tournament. The IDs of the black player and the white player are referenced to another table called 'players', where the id is unique.

In my failed attempts I have tried to achieve something like this:

So I need a table that sums up both the points of the games played as 'black' and those played as 'white'.

I'm not sure how I can do this in sql (currently working on PostgreSQL). Thank you in advance!

CodePudding user response：

Sum the points as white by player id and the points as black by player id, stack them on top of each other, then sum all the points by player id, regardless of color played.

SELECT player_id, SUM(points) AS points
FROM (
    SELECT id_white_player AS player_id, SUM(white_points) AS points
    FROM games
    GROUP BY id_white_player
    UNION ALL
    SELECT id_black_player AS player_id, SUM(black_points) AS points
    FROM games
    GROUP BY id_black_player
) points
    GROUP BY player_id;

CodePudding user response：

you can stack the results and then sum.

e.g.

with united AS (
select id_white_player player_id, white_points points
union all
select id_black_player player_id, black_points points
)
select player_id, sum(points) sum_points
group by 1

CodePudding user response：

You need to build first a resultset that selects all the points associated to a given ID, weither it's black or white, then use aggregate functions to sum them all. The easiest way that comes to my mind is using a UNION clause associated to a CTE:

WITH score(id,points) AS (
    SELECT id_white_player,
           white_points
      FROM your_table

 UNION ALL

    SELECT id_black_player,
           black_points
      FROM your_table
)

  SELECT id,sum(points)
    FROM score
GROUP BY id
ORDER BY id ASC;