otis otis - 6 months ago 24
MySQL Question

sum of two columns in two tables

I have two tables and need to get sum of two columns (one in each table).


  • table Games(id_tournament,id_game,email,points) PK(id_tournament,id_game,email)

  • table Results(id_tournament,email,points) PK(id_tournament,email)

  • table User(id_user,email,name) PK(id_user)



example table Results:

1,me@hotmail.com,3
1,you@hotmail.com,4


example table Games:

1,1,me@hotmail.com,5
1,2,me@hotmail.com,3
1,3,me@hotmail.com,2
1,1,you@hotmail.com,4
1,3,you@hotmail.com,2


example table User:

1,me@hotmail.com,John
2,you@hotmail.com,Peter


My output should be:

John 13
Peter 10


I tried to use

SELECT t3.name, SUM(t1.points+t2.points) as Total
FROM user t3, games t1, results t2
WHERE t1.email=t2.email AND t2.email=t3.email AND t1.id_tournament=t2.tournament
GROUP BY t1.email
ORDER BY Total DESC


but got very weird numbers.

Answer

Get the total points from the games table, and total points from results table separately, and then total them.

Something like this:

 SELECT v.name
      , SUM(v.points) AS points
   FROM (
          SELECT gu.name
               , SUM(g.points) AS points
            FROM games g
            JOIN user gu
              ON gu.email = g.email
           GROUP BY gu.name

           UNION ALL

          SELECT ru.name
               , SUM(r.points) AS points
            FROM results r
            JOIN user ru
              ON ru.email = r.email
           GROUP BY ru.name   
        ) v
    GROUP BY v.name
    ORDER BY v.name

EDIT

Added missing GROUP BY clauses.

If we need to return all values of name from the user table, even when there are no related rows in games or results...

 SELECT n.name
      , IFNULL(SUM(v.points),0) AS points
   FROM users n
   LEFT
   JOIN (
          SELECT gu.name
               , SUM(g.points) AS points
            FROM games g
            JOIN user gu
              ON gu.email = g.email
           GROUP BY gu.name

           UNION ALL

          SELECT ru.name
               , SUM(r.points) AS points
            FROM results r
            JOIN user ru
              ON ru.email = r.email
           GROUP BY ru.name   
        ) v
      ON v.name = n.name
    GROUP BY n.name
    ORDER BY n.name