Multiple COUNT on same table

user_9397897 0
Viewed 125

I have one table:

   +----------+-----------+----------+--------+
    | id       |  winnerId | loserId  | gameId |
    +----------+-----------+----------+--------+
    | 1        | user1     | user2    |      1 |
    | 2        | user2     | user1    |      1 |
    | 3        | user1     | user2    |      1 |
    | 4        | user1     | user2    |      2 |
      5          user1       user3           1
    +----------+-----------+----------+--------+

actually i have more columns in table but they are unreleated but please consider that

Given winnerId,loserId and gameId i would like to get total scores(win count) of winner and loser players against each other

Example query input:

winnerId:user1
loserId:user2
gameId:1

Result:

   --userId--totalScore--
       user1      2           
       user2      1
mysql sql
2 Answers

Select the rows where the two players played against each other. Then count the rows per winner for the game:

select winnerid, count(*) 
from mytable
where gameid = 1
and (winnerid, loserid) in ((1,2), (2,1))
group by winnerid;

Demo: https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=976df92c9706c08d34a1c372735efa4c

EDIT: If one of the players never won against the other, there will be no row to count. If you want to see a result row with a count of zero in that case, then you must first create a row. One way is this:

select playerid, sum(won) 
from 
(
  select winnerid as playerid, 1 as won
  from mytable
  where gameid = 1
  and (winnerid, loserid) in ((1,2), (2,1))
  union all 
  select loserid as playerid, 0 as won
  from mytable
  where gameid = 1
  and (winnerid, loserid) in ((1,2), (2,1))
)
group by playerid
order by playerid;
user_22707620

This sounds like aggregation and filtering:

select winnerId as userId, count(*) as num_wins
from t
where ? in (winnerId, loserId) and
      ? in (winnerId, loserId) and
      gameId = ?
group by winnerId
user_11440350
Related