A rookie question about group by

1

Hi, first of all, am a beginner and would't write here for a rookie question, but I really could handle it out.

Let's say we have this table:

id| first_player_id | second_player_id | winner_id
1         1                 2                2
2         2                 3                2
3         2                 1                2
4         2                 3                2
5         1                 2                2
6         4                 2                2
7         4                 2                2
8         4                 2                2
9         4                 2                2
10        1                 2                2

So, my goal is the create a SQL query, that shows versus why a given player_id has most wins.
If I use this:

WHERE first_player_id == 2 and m.winner_id == 2,
        group_by: m.second_player_id,
        order_by: [desc: count(m.id)],
        select: {m.second_player_id, count(m.id)},
        limit: 1

that will give us second_player 3 with 2 counts.
Vice versa, if I search for second_player_id == 2, I will get first_player_id = 4 with count = 4

However, If I look for values, where

(first_player_id == 2 or second_player_id == 2) and winner_id == 2

I get see, that vs id 1 there are most matches. How to write this (group by) in SQL?

3

Yeah, this gets the most frequent player_id. However, my goal is by having a given player_id, to get most values (in context: most matches) with another player_id.

i.e.: we have player with id 2. That player has most matches with ....? (player_id).

Sorry if I mislead with the description in the first post

5

In future, please post consumable test data and expected results. With:

SET ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING ON;
GO
CREATE TABLE #Players
(
	player_id int NOT NULL PRIMARY KEY
);
INSERT INTO #Players
VALUES (1),(2),(3),(4);
GO
CREATE TABLE #Matches
(
	match_id int NOT NULL PRIMARY KEY
	,first_player_id int NOT NULL
	,second_player_id int NOT NULL
	,winner_player_id int NOT NULL
);
INSERT INTO #Matches
VALUES (1, 1, 2, 2)
	,(2, 2, 3, 2)
	,(3, 2, 1, 2)
	,(4, 2, 3, 2)
	,(5, 1, 2, 2)
	,(6, 4, 2, 2)
	,(7, 4, 2, 2)
	,(8, 4, 2, 2)
	,(9, 4, 2, 2)
	,(10, 1, 2, 2);

This should get you started:

SELECT P.player_id
	,X.opponent_player_id
	,COUNT(1) AS matches
	,SUM(X.win) AS wins
FROM #Players P
	JOIN #Matches M
		ON P.player_id = M.first_player_id
			OR P.player_id = M.second_player_id
	CROSS APPLY
	(
		VALUES
		(
			CASE
				WHEN P.player_id = M.first_player_id
				THEN M.second_player_id
				ELSE M.first_player_id
			END
			,CASE
				WHEN P.player_id = M.winner_player_id
				THEN 1
				ELSE 0
			END
		)
	) X (opponent_player_id, win)
GROUP BY P.player_id, X.opponent_player_id
ORDER BY wins DESC, player_id, opponent_player_id;

ps You probably also need to understand this:
SQL SERVER - Logical Processing Order of the SELECT Statement - SQL Authority with Pinal Dave

1 Like
6

hi

hope this helps

i was trying to do this in a simple way without much code
created a mapping table and did it

create data script

drop table #Mapping

create table #Mapping ( id1 int , id2 int , grping int )
insert into #Mapping select 1,2,1
insert into #Mapping select 1,3,2
insert into #Mapping select 1,4,3
insert into #Mapping select 2,1,1
insert into #Mapping select 2,3,4
insert into #Mapping select 2,4,5
insert into #Mapping select 3,1,2
insert into #Mapping select 3,2,4
insert into #Mapping select 3,4,6
insert into #Mapping select 4,1,3
insert into #Mapping select 4,2,5
insert into #Mapping select 4,3,6

drop table #Player
CREATE TABLE #Player
(
match_id int NOT NULL PRIMARY KEY
,first_player_id int NOT NULL
,second_player_id int NOT NULL
,winner_player_id int NOT NULL
);
INSERT INTO #Player
VALUES (1, 1, 2, 2)
,(2, 2, 3, 2)
,(3, 2, 1, 2)
,(4, 2, 3, 2)
,(5, 1, 2, 2)
,(6, 4, 2, 2)

select 
    grping
	, min(first_player_id) as first_player_id
	, max(second_player_id) as second_player_id
	, count(*) 
from 
    #Player a join #Mapping b on a.first_player_id = b.id1 and a.second_player_id = b.id2
group by 
   grping

image
image524×549 6.87 KB