CODEWITHSUNDEEP
mysqlsql
user3552447
user3552447

Reputation: 1

joining 3 tables with sum total from two columns meeting a certain criteria

Say I have 3 tables:

Players
+------+-------+
| id   | name  |
+------+-------+
| 1    | Ben   |
| 2    | Bill  |
| 3    | John  |
| 4    | Hugh  |
| 5    | Fred  |
+------|-------+

     Game1              Game2
+------+-------+    +------+-------+
| p_id | points|    | p_id | points| 
+------+-------+    +------+-------+
| 1    | 800   |    | 1    | 300   |
| 1    | 400   |    | 5    | 800   |
| 2    | 100   |    | 2    | 900   |
| 3    | 1000  |    | 4    | 1200  |
+------|-------+    +------|-------+

I want to sum the total of points from both tables, grouped by p_ID where the sum is greater than a certain amount, 1000 for example. So i would want the result to look like this.

Query Result
+------+---------+---------+--------+
| p_id | points1 | points2 | total  |
+------+---------+---------+--------+
| 1    |  1200   |   300   |  1500  |
| 2    |  100    |   900   |  1000  |
| 3    |  1000   |    0    |  1000  |
| 4    |   0     |   1200  |  1200  |
+------|---------+---------+--------+

Below is my current query but I'm not getting the results I need.

SELECT `players`.`p_id`, 
COALESCE(sum(`Game1`.`points`), 0) as 'Game1', 
COALESCE(sum(`Game2`.`points`), 0) as 'Game_2', 
COALESCE(sum(`Game1`.`points`), 0) + COALESCE(sum(`Game2`.`points`), 0) as 'total' 
from `players` 
left join `Game1` on `players`.`p_id`=`Game1`.`p_id` 
left join `Game2` on `players`.`p_id`=`Game2`.`p_id` 
having sum(`Game2`.`points`) + sum(`Game1`.`points`) >= 1000 
order by sum(`Game1`.`points`) + 
sum(`Game2`.`points`) desc

Upvotes: 0

Views: 65

Answers (2)

Eugen Rieck
Eugen Rieck

Reputation: 65274

SELECT
  players.id AS p_id,
  IFNULL(points1,0) AS Game1,
  IFNULL(points2,0) AS Game2,
  IFNULL(points1,0)+IFNULL(points2,0) AS total
FROM players
  LEFT JOIN (
    SELECT p_id, SUM(Game1.points) AS points1
    FROM Game1
    GROUP BY p_id
  ) AS GameSum1 ON players.id=GameSum1.p_id
  LEFT JOIN (
    SELECT p_id, SUM(Game2.points) AS points2
    FROM Game2
    GROUP BY p_id
  ) AS GameSum2 ON players.id=GameSum2.p_id
GROUP BY players.id
HAVING total>=1000
ORDER BY total DESC

EDIT

Fixed wrong IFNULL() scope, see SQLfiddle

EDIT 2

Fixed according to @GordonLinoff's input

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269643

In order for this to work correctly, you need to do the aggregation before the join. Otherwise, you end up with issues related to a cartesian product for each game.

Because MySQL does not support full outer join, the best way is to use union all instead:

select p_id, sum(points1) as points1, sum(points2) as points2
from ((select p_id, points as points1, 0 as points2
       from Game1
      ) union all
      (select p_id, 0, points
       from Game2
      )
     ) g
group by p_id;

Upvotes: 2

Related Questions