Question 245215 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL Query: Select rows with repeated values across different columns

I have a mysql table that contains goalscorer information for a list of soccer matches. The table is structured like this:

ID S1 S2 S3 S4 S5 S6
1 11 11 18 9 0 0
2 10 3 10 0 0 0
3 10 11 11 18 11 0
4 11 11 18 9 0 0
5 0 0 0 0 0 0
6 7 10 0 0 0 0
… … … … … … …

In this table, ID corresponds to an individual match ID – where as the numbers under each Sx column identify individual players and are cross-referenced with another table.

The following query lets me identify instances where player 11 scored more than once

SELECT * 
FROM `matchscorer` WHERE (`S1` = '11' AND (`S2` = '11' OR `S3` = '11' OR `S4` = '11' OR `S5` = '11' OR `S6` = '11')) OR (`S2` = '11' AND (`S3` = '11' OR `S4` = '11' OR `S5` = '11' OR `S6` = '11')) OR (`S3` = '11' AND (`S4` = '11' OR `S5` = '11' OR `S6` = '11')) OR (`S4` = '11' AND (`S5` = '11' OR `S6` = '11')) OR (`S5` = '11' AND (`S6` = '11'));

This returns the rows in which 11 appears more than once across the various columns – and I can then manually count those rows to get a total for how many times that happened.

What I wish to achieve, however, is a query that will identify occurrences throughout the table such as this for all players, then (I think) using GROUP BY to then output a list showing how many times a player scored more than once e.g. Player 11 – 3, Player 10 – 2.

The main blocker I have at the moment is adapting the query from a single player (11) to any player. Nothing I’ve found through research so far seems to focus on this type of query. Should I instead use SELECT DISTINCT or COUNT DISTINCT statements to get the desired output.

Is it then possible to isolate the query even further for instances of a player scoring just twice, or thrice, or even four times? Would this require something like count = 3?

Tags:

2

Answers


  1. 0

    Assuming Sx columns are indexes of goals scored

    You can create a new table with the following columns:
    match_id | player | goal_index

    Then, you can group this table by match_id and player columns, and count how many times each player scored.
    From there you should be able to easily find players who scored in N games.

    SELECT
match_id, player, count(*) as goals_scored
FROM
(SELECT 
`ID` as match_id, `S1` as player, '1' as goal_index
FROM `matchscorer`
WHERE `S1`<>0

UNION ALL

SELECT 
`ID` as match_id, `S2` as player, '2' as goal_index
FROM `matchscorer`
WHERE `S2`<>0

SELECT 
`ID` as match_id, `S3` as player, '3' as goal_index
FROM `matchscorer`
WHERE `S3`<>0

SELECT 
`ID` as match_id, `S4` as player, '4' as goal_index
FROM `matchscorer`
WHERE `S4`<>0

SELECT 
`ID` as match_id, `S5` as player, '5' as goal_index
FROM `matchscorer`
WHERE `S5`<>0

SELECT 
`ID` as match_id, `S6` as player, '6' as goal_index
FROM `matchscorer`
WHERE `S6`<>0) as converted_table
GROUP BY 1,2
    Login or Signup to reply.
  2. 0

    If you are using MySQL ≥ 8.0.14, you can CROSS JOIN to a lateral derived table to unpivot the data, as suggested by GarethD in the comments:

    WITH match_goals (match_id, player_id, num_goals) AS (
    SELECT m.ID, s.player_id, COUNT(*)
    FROM matchscorer m
    CROSS JOIN LATERAL (
        SELECT S1 UNION ALL SELECT S2 UNION ALL SELECT S3 UNION ALL SELECT S4 UNION ALL
        SELECT S5 UNION ALL SELECT S6 UNION ALL SELECT S7 UNION ALL SELECT S8 UNION ALL
        SELECT S9 UNION ALL SELECT S10 UNION ALL SELECT S11
    ) AS s (player_id)
    WHERE s.player_id > 0
    GROUP BY m.ID, s.player_id
)
SELECT
    player_id,
    SUM(num_goals = 1) AS scored_1,
    SUM(num_goals = 2) AS scored_2,
    SUM(num_goals = 3) AS scored_3,
    SUM(num_goals = 4) AS scored_4
FROM match_goals
GROUP BY player_id
ORDER BY player_id;

    Or, if you are using MySQL < 8.0.14, you can use a CROSS JOIN to a derived table and then use the CASE operator to switch columns:

    SELECT
    player_id,
    SUM(num_goals = 1) AS scored_1,
    SUM(num_goals = 2) AS scored_2,
    SUM(num_goals = 3) AS scored_3,
    SUM(num_goals = 4) AS scored_4
FROM (
    SELECT match_id, player_id, COUNT(*) AS num_goals
    FROM (
        SELECT ID AS match_id,
            CASE n.s
                WHEN  1 THEN S1
                WHEN  2 THEN S2
                WHEN  3 THEN S3
                WHEN  4 THEN S4
                WHEN  5 THEN S5
                WHEN  6 THEN S6
                WHEN  7 THEN S7
                WHEN  8 THEN S8
                WHEN  9 THEN S9
                WHEN 10 THEN S10
                WHEN 11 THEN S11
            END AS player_id,
            n.s
        FROM matchscorer
        CROSS JOIN (
            SELECT 1 AS s UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
            SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
            SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11
        ) n
        HAVING player_id > 0
    ) t1
    GROUP BY match_id, player_id
) t2
GROUP BY player_id
ORDER BY player_id;

    For the sample data provided, both of the above queries will output:

    player_id scored_1 scored_2 scored_3 scored_4
    3 1 0 0 0
    7 1 0 0 0
    9 2 0 0 0
    10 2 1 0 0
    11 0 2 1 0
    18 3 0 0 0

    As suggested by Sitethief in the comments, you really should consider restructuring your data. If you create a new table with (match_id, player_id, goal_index), you can easily populate it using the inner queries above. You could replace the existing matchscorer table with a view so that existing SELECT queries still work. Then you would only need to update parts of your application that write to the table. Easy for me to say with no knowledge of the scope/scale of your app 😉

    Here’s a db<>fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search