CODEWITHSUNDEEP
sqlpostgresql
Rallyx
Rallyx

Reputation: 48

Create uniques couple of value and count their occurence with same value in other columns

I'm new in SQL world, don't be too rude with me...

I have a simple SQL table for an only game with :

I want to group all players by pair Player_1, Player_2 and count how many times they logged on the same server.

For example with the table :

ServerName | PlayerName
_ _ _ _ _ _ _ _ _ _ _ _ 
    S1     |    a 
    S3     |    a 
    S1     |    b
    S3     |    c 
    S4     |    b
    S4     |    a

I want to be able to produce :

a b 2
a c 1
b c 0

.. and so for all unique couple (Player_1, Player_2)

I tryed some technique with join on the table with itself and the distinct key but nothing as I want.

I'm not sure to clearly understand the behavior of 'Order By' function.

I'm working on postgresql.

Upvotes: 0

Views: 50

Answers (2)

Philipp Johannis
Philipp Johannis

Reputation: 2956

You should be able to solve it with joining the table to itself, let's assume the table is called game

SELECT g1.playername,g2.playername,COUNT(1) games
FROM game g1
JOIN game g2
   ON g1.servername = g2.servername
   AND g1.playername < g2.playername
GROUP BY g1.playername,g2.playername
ORDER BY g1.playername,g2.playername

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270431

If you want zeros then cross join all players and left join to aggregate:

select p1.playername, p2.playername,
       count(t2.servername)
from players p1 join
     players p2
     on p1.playername < p2.playername left join
     games g1
     on g1.playername = p1.playername left join
     games g2
     on g2.playername = p2.playername and
        g2.servername = g1.servername
group by p1.playername, p2.playername;

Upvotes: 1

Related Questions