SQL query to aggregate result of a column comparison

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).

match_id	player_one	player_two	p1_game_result	p2_game_result
1	player1	player2	12	10
1	player1	player2	3	11
1	player1	player2	5	9
2	player1	player3	11	2
2	player1	player3	11	1
3	player2	player4	11	6
3	player2	player4	7	11
3	player2	player4	9	5

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).

match_id	player_one	player_two	p1_games_won	p2_games_won
1	player1	player2	1	2
2	player1	player3	2	0
3	player2	player4	2	1

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.

Answer

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;