CODEWITHSUNDEEP
sqlsql-server
Carlos Alves Jorge
Carlos Alves Jorge

Reputation: 1985

GROUP BY to get several aggregates per row

I have a table GAMES where I have the ids of player_1, player_2 and the winner

player_1   player_2   winner
1          2          1
1          2          1
1          3          3
2          3          2
2          1          2

I want to make a query where the end result would be similar to:

players     num_games_played   wins  
1|2         3                  2|1
1|3         1                  0|1
2|3         1                  1|0

Basically I need to group by the results to show a list of games played and number of wins per player.

I'm not really sure whether I should stick with this sort of table or make some relationship tables.

Any suggestion of how the best approach would be?

Upvotes: 1

Views: 61

Answers (3)

Premjit Chowdhury
Premjit Chowdhury

Reputation: 76

This is another code which is using similar ideas from above SQL query

with demo_solution as (
select 
p1=case when player1>player2 then player2 else player1 end,
p2=case when player1>player2 then player1 else player2 end,
winner
from Games
)
select 
players = cast(p1 as varchar)+'|'+cast(p2 as varchar),
num_games_played = count(1),
wins = cast(sum(case when winner=p1 then 1 else 0 end) as varchar)
     +'|'+
    cast(sum(case when winner=p2 then 1 else 0 end) as varchar)
from demo_solution
group by cast(p1 as varchar)+'|'+cast(p2 as varchar)

Upvotes: 0

Sean Lange
Sean Lange

Reputation: 33571

Here is one way you can tackle this. 

if OBJECT_ID('tempdb..#Games') is not null
    drop table #Games

create table #Games
(   
    player1 int
    , player2 int
    , winner int
)

insert #Games values
(1, 2, 1)
, (1, 2, 1)
, (1, 3, 3)
, (2, 3, 2)
, (2, 1, 2)

select players = convert(varchar(5), player1) + '|' + convert(varchar(5), player2)
    , num_games_played = count(*)
    , convert(varchar(5), sum(case when winner = player1 then 1 else 0 end)) + '|' + convert(varchar(5), sum(case when winner = player2 then 1 else 0 end))
from
(
    select player1
        , player2
        , winner
    from #Games
    where player1 < player2

    union all

    select player2
        , player1
        , winner
    from #Games
    where player2 < player1
) x
group by convert(varchar(5), player1) + '|' + convert(varchar(5), player2)

Upvotes: 1

JoseR
JoseR

Reputation: 76

I would use some relational tables, It will be easy for you to get the results and also will help you if you later make some updates that allows from more than two players. Maybe one table with the game/match information and another table linked to the game table where you have the players that played the match and a bit field for the result (winner).

Upvotes: 0

Related Questions