I have a SQL (Postgres) data structure that represents games for a group of matches. Table below shows that there have been 3 matches played (each consisting of either 2 or 3 games).

I want to group it by matchID to the following result table - each row should represent single match that will show how many games has each player won (which simply mean which out of 2 results column will be greater).

I'm aware how GROUP BY works, but I'm not able to use aggregate function that will apply required condition based on column values.

Query below counts games for each match only, without distinguishing how many games has each player won in a match.

SELECT
    g.match_id,
    g.player_one,
    g.player_two,
    count(*) as games_count
FROM games g
GROUP BY
    g.match_id,
    g.player_one,
    g.player_two;

Any help will be appreciated.

CodePudding user response：

Use a CASE statement inside your COUNT to compare the two columns e.g.

SELECT
    g.match_id,
    g.player_one,
    g.player_two,
    count(CASE WHEN p1_game_result > p2_game_result THEN 1 END) as p1_games_won,
    count(CASE WHEN p2_game_result > p1_game_result THEN 1 END) as p_games_won
FROM games g
GROUP BY
    g.match_id,
    g.player_one,
    g.player_two;