CODEWITHSUNDEEP
sql
mrfreester
mrfreester

Reputation: 1991

Count Results In Column Connected With Distinct ID SQL

I've done a lot of work getting my results to this point, and now I'm stuck. It's been difficult for me to find a case like this online.

I have a table essentially like this:

Name   |  Results
John   |  Win
John   |  Loss
John   |  Tie
Jim    |  Win
Jim    |  Win

This table is derived from different games, so each row essentially represents a players result for a game, and there should be two rows per game since each game has 2 players.

Anyway, I want to get some results like this and I've tried all sorts of weird stuff with Counts but I'm having a hard time getting a distinct name with the correct counts. Here is what I would like:

NAME   | games_won   |  games_lost   |  games_tied
John   |      1      |       1       |       1
Jim    |      2      |       0       |       0

And as a side note, here is the incredibly messy SQL statement I've come up with to get the first table, except I currently have it also giving the game id like this:

name  |  game_id  | result

Only read on here if you're up for a good laugh, I'm 99% sure there is a much simpler way to do this, but just remember my question here isn't about how to improve what I've written below, it's just about what to do with the result:

SELECT name, game_id,
    case
        when p1_score>p2_score then 'win'
        when p1_score<p2_score then 'loss'
        else 'tie'
    end as result
from player 
Inner JOIN game on player.player_id = game.p1_id

union select * from (SELECT name, game_id,
    case
        when p1_score>p2_score then 'win'
        when p1_score<p2_score then 'loss'
        else 'tie'
    end as result
from player 
Inner JOIN game on player.player_id = game.p2_id) as ta

Upvotes: 1

Views: 73

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

SELECT p.name,
    sum(case when p.player_id = g.p1_id and g.p1_score>g.p2_score
         or p.player_id = g.p2_id and g.p1_score<g.p2_score then 1 else 0 end) games_won,
    sum(case when p.player_id = g.p1_id and g.p1_score<g.p2_score
         or p.player_id = g.p2_id and g.p1_score>g.p2_score then 1 else 0 end) games_lost,
    sum(case when g.p1_score=g.p2_score then 1 else 0 end) games_tied
from player p
inner JOIN game g on p.player_id in (g.p1_id, g.p2_id)
group by p.name

Upvotes: 3

Related Questions