Multiple aggregate count columns SQL

Question

I have a table that roughly looks like the following:

winner_name	loser_name
Person A	Person B
Person A	Person C
Person B	Person A
Person C	Person B

I'm trying to return a table that looks like the following:

player_name	number_wins	number_losses
Person A	2	1
Person B	1	2
Person C	1	1

I can't quite figure out how to get there. I have been able to write a query that returns player_name and either number_wins or number_losses, but not both.

SELECT winner_name AS player_name, COUNT(*) AS number_wins
FROM my_table
GROUP BY player_name

I have looked into using procedures, functions, and subqueries to do this but I haven't found the right solution yet. Any direction would be helpful.

Barmar · Accepted Answer · 2021-11-11 02:08:13Z

1

You need to pivot the names into the same column using UNION. Then you can calculate the sum of wins and losses.

SELECT player_name, SUM(win) AS number_wins, SUM(loss) AS number_losses)
FROM (
    SELECT winner_name AS player_name, 1 AS win, 0 AS loss
    FROM my_table
    UNION ALL
    SELECT loser_name AS player_name, 0 AS win, 1 AS loss
    FROM my_table
) AS x
GROUP BY player_name

answered Nov 11, 2021 at 2:08

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

outis · Accepted Answer · 2022-06-21 18:21:22Z

Since each aggregate statistic is for a different group (one for winner_name, the other for loser_name), they can't be calculated in the same query, but each query can be run separately and then combined with a JOIN. Simply take each query:

SELECT winner_name AS player, COUNT(loser_name) AS wins
  FROM games 
  GROUP BY winner_name
;

SELECT loser_name AS player, COUNT(winner) AS losses
  FROM games
  GROUP BY loser_name
;

and join on the common attribute, the player name:

SELECT gw.player, gw.wins, gl.losses
  FROM (
      SELECT winner_name AS player, COUNT(loser_name) AS wins
        FROM games 
        GROUP BY winner_name
      ) AS gw
    JOIN (
      SELECT loser_name AS player, COUNT(winner_name) AS losses
        FROM games
        GROUP BY loser_name
      ) AS gl
      ON gl.player = gw.player
;

Whether using unions or joins, each distinct group that is the basis for aggregate statistics will require a separate sub-select.

Collectives™ on Stack Overflow

Multiple aggregate count columns SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related