otis - 5 months ago 13
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.

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
ON gu.email = g.email
GROUP BY gu.name

UNION ALL

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

EDIT

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
ON gu.email = g.email
GROUP BY gu.name

UNION ALL

SELECT ru.name
, SUM(r.points) AS points
FROM results r