Home / Questions / 482537
Accepted
Pablo Grassi
Asked: 2022-09-07 08:25:01 +0800 CST 2022-09-07 08:25:01 +0800 CST

MYSQL query formulation problem

  • 772

I have a table of matches where I want to obtain the sum of the points of the players, the player is crossed with the player table where the id is the identifier, in the game table the player can appear in both player_a and player_b, that is, when I do the sum of the points of the players I look for the id of the player in the two columns. I get stuck a bit with the query and I can't solve it, it adds the points only when the id corresponds to player_a, but it doesn't add when the points correspond to the id of player_b.

SELECT
    (puntos_a + puntos_b) AS puntos,
    id
FROM
    (
    SELECT
        SUM(puntos_jugador_a) AS puntos_a,
        0 AS puntos_b,
        jugador_a AS id
    FROM
        game
    WHERE
        torneo_organizacion = "MZF-CAB"
    GROUP BY
        jugador_a
    UNION ALL
SELECT
    0 AS puntos_a,
    SUM(puntos_jugador_b) AS puntos_b,
    jugador_b AS id
FROM
    game
WHERE
    torneo_organizacion = "MZF-CAB"
GROUP BY
    jugador_b
) t
GROUP BY
    id
mysql

1 Answers

  1. Best Answer

    I think the simplest is to do it as follows:

    SELECT
    torneo_organizacion,
    jugador,
    SUM( IF( jugador_a = jugador, puntos_jugador_a, puntos_jugador_b ) ) AS puntos
FROM game
INNER JOIN (
  SELECT DISTINCT(jugador_a) jugador
  FROM game
  UNION SELECT DISTINCT(jugador_b) jugador
  FROM game
) jugadores
    ON jugador IN (jugador_a, jugador_b)
GROUP BY torneo_organizacion, jugador;

    I have divided the query into two: get the list of players and get the sum of the points.

    To begin with, I obtain the complete list of players through the following query:

    SELECT DISTINCT(jugador_a) jugador
FROM game
UNION SELECT DISTINCT(jugador_b) jugador
FROM game

    Later, with that list of players, we can obtain the sum of points in a similar way as I indicated before:

    SUM( IF( jugador_a = jugador, puntos_jugador_a, puntos_jugador_b ) )

    Add the points of puntos_jugador_aif this is jugador_aor, if not, add the points of puntos_jugador_b.

    You can see the query work online at the following link:

    • 4