I'm trying to count a set of values in a mysql table, where I'm not sure if there is an easy way to do it directly in mysql or if it would be easier to just do it in any programming language after fetching all results.

So here's a simplified version of my table and I want to count the names like a team so "susi" and "anton" would get COUNT 2 because they were in Game 2 and 4 together and all others would get have COUNT 1 because they were only in one game together.

Game-ID Name
1 mike
1 anton
1 johann
1 susi
2 anton
2 susi
3 johann
3 mike
4 susi
4 anton

I tried messing around with GROUP BY but that didn't really got me any useful results. Thanks in advance

  • You imply that you do not want johann+mike; is this correct?
    – Rick James
    Commented May 5, 2023 at 19:14
  • @RickJames Yes and neither do I want susi and anton counted as 3. All players have to be present to be counted.
    – FiFT
    Commented May 6, 2023 at 8:13

2 Answers 2

SELECT player_list, COUNT(*) AS match_ct
  FROM (
    SELECT game_id, GROUP_CONCAT(name ORDER BY name) AS player_list
        FROM test
        GROUP BY game_id
       ) AS x
  GROUP BY player_list
  HAVING match_ct > 1
  • Add row (2, 'john')
    – Akina
    Commented May 5, 2023 at 20:11
  • @Akina - Then the output will be empty. There may be a question of the meaning of "players in the same game".
    – Rick James
    Commented May 6, 2023 at 0:24
  • Your interpretation of "players in the same game" is correct, it should only count if all players were in the game. So it doesn't matter susi and anton were in game 1 as well because there were 4 players in the game.
    – FiFT
    Commented May 6, 2023 at 8:01
SELECT t1.name name1, t2.name name2
FROM test t1
JOIN test t2 USING (game_id)
WHERE t1.name < t2.name

step-by-step fiddle

  • Your solution is not quite what I need in this case as it counts even if not all players were present (see my comment under the accepted answer) but I might have a use for it in some other cases, thank you.
    – FiFT
    Commented May 6, 2023 at 8:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.