Home / Questions / 482437
Accepted
Pablo Grassi
Asked: 2022-09-06 20:16:36 +0800 CST 2022-09-06 20:16:36 +0800 CST

Join two SQL sums in a single query

  • 772

I want to join these two queries into one and add the two results:

SELECT torneo_organizacion, jugador_a, SUM( puntos_jugador_a ) AS total FROM game
WHERE torneo_organizacion = "MZF-CAB" AND jugador_a = 125
AND fecha_partido >= ( CURDATE( ) - INTERVAL 365 DAY ) 

SELECT torneo_organizacion, jugador_b, SUM( puntos_jugador_b ) AS total FROM game
WHERE torneo_organizacion = "MZF-CAB" AND jugador_b = 125
AND fecha_partido >= ( CURDATE( ) - INTERVAL 365 DAY )
mysql

2 Answers

  1. Best Answer

    You can have both results, as player A and as player B:

    SELECT
    SUM( IF(jugador_a = 125, puntos_jugador_a, 0) ) AS puntos_jugador_a,
    SUM( IF(jugador_b = 125, puntos_jugador_b, 0) ) AS puntos_jugador_b
FROM game
WHERE
    torneo_organizacion = "MZF-CAB"
    AND 125 IN (jugador_a, jugador_b)
    AND fecha_partido >= ( CURDATE( ) - INTERVAL 365 DAY );

    In the SELECTwe have two sums, which will add the content of puntos_jugador_aor puntos_jugador_bonly in case the player we are adding is the aor the b(sum 0in another case).

    You can also get the total data using the following query:

    SELECT
    SUM( IF(jugador_a = 125, puntos_jugador_a, puntos_jugador_b) ) AS puntos
FROM game
WHERE
    torneo_organizacion = "MZF-CAB"
    AND 125 IN (jugador_a, jugador_b)
    AND fecha_partido >= ( CURDATE( ) - INTERVAL 365 DAY );

    In this case, add the points puntos_jugador_aif it is the player aor puntos_jugador_botherwise (it is assumed that it will be the b).

    Both queries share the same WHERE:

    AND 125 IN (jugador_a, jugador_b)

    In which the records whose jugador_aor jugador_bthe one we are analyzing are searched.

    You can see the operation online at the following link:

    • 5

  2. SELECT SUM( puntos_jugador_a + (SELECT SUM( puntos_jugador_b ) AS total FROM game WHERE torneo_organizacion = "MZF-CAB" AND jugador_b = 125 AND fecha_partido >= ( CURDATE( ) - INTERVAL 365 DAY ))) AS total FROM game WHERE torneo_organizacion = "MZF-CAB" AND jugador_a = 125 AND fecha_partido >= ( CURDATE( ) - INTERVAL 365 DAY )

    If this query is correct, the only thing it will give you is the sum of the points, which is what you want to obtain. I don't know if it's the best way. Also, it comes out of the code you provided, so if your queries were working, they should work.

    Subject I recommend studying:

    SUBQUERIES

    https://www.youtube.com/watch?v=rGPb5E1UAJA

    https://www.youtube.com/watch?v=lCpMJ2LFdLg

    • 1