CODEWITHSUNDEEP
sqlpostgresql
ditto
ditto

Reputation: 6277

How to get total wins for two players playing together?

I have this table named playergame.

id gameid player win
1 1 jon true
2 1 dan true
3 1 lee false
4 2 jon false
5 2 jon true
6 3 lee true

I want to get all games where jon and dan have won playing together. I think I may be able to do this with something like:

SELECT count(*) as wins
FROM playergame
WHERE player = 'dan' AND player = 'jon' AND win = true
GROUP BY gameid

However I need to factor in that I'm wanting to merge and query two rows into 1. How can I do this?

Upvotes: 1

Views: 84

Answers (2)

AlexanderP
AlexanderP

Reputation: 126

PIVOT will give you a table format that works for your purpose. With PIVOT you can generate a table that has one line per gameid and then one column per player and the value true and false for each player for each game id.

To learn more about how to use PIVOT, have a look here.

Then you can use the query

SELECT count(*) as wins
FROM playergame_pivot
WHERE Column_Dan = true AND Column_Jon = true

Upvotes: 0

eshirvana
eshirvana

Reputation: 24568

if you only want gameid , here is the simplest way :

select gameid 
from playergame
where player in ('jon' , 'don')
and win = true
group by gameid
having count(distinct player) = 2

Upvotes: 5

Related Questions