Using SUM with multiple joins in mysql

Question

I've been looking for a solution to this, there's plenty of similar questions but none have any proper answers that helped me solve the problem.

First up, my questions/problem:

1. I want to sum and count certain columns in a multiple join query
2. Is it not possible with multiple joins? Do I have to nest SELECT queries?

Here's a SQL dump of my database with sample data: http://pastie.org/private/vq7qkfer5mwyraudb5dh0a

This is the query I thought would do the trick:

SELECT firstname, lastname, sum(goal.goal), sum(assist.assist), sum(gw.gw), sum(win.win), count(played.idplayer) FROM player
LEFT JOIN goal USING (idplayer)
LEFT JOIN assist USING (idplayer)
LEFT JOIN gw USING (idplayer)
LEFT JOIN win USING (idplayer)
LEFT JOIN played USING (idplayer)
GROUP BY idplayer

What I'd like this to produce is a table where the columns for goal, assist, gw, win and played are a sum/count of every row in that column, like so: (with supplied sample data)

+-----------+----------+------+--------+----+-----+--------+
| firstname | lastname | goal | assist | gw | win | played |
+-----------+----------+------+--------+----+-----+--------+
| Gandalf   | The White|   10 |      6 |  1 |   1 |      2 |
| Frodo     | Baggins  |   16 |      2 |  1 |   2 |      2 |
| Bilbo     | Baggins  |    7 |      3 |  0 |   0 |      2 |
+-----------+----------+------+--------+----+-----+--------+

So, to iterate the above questions again, is this possible with one query and multiple joins?

If you provide solutions/queries, please explain them! I'm new to proper relational databases and I have never used joins before this project. I'd also appreciate if you avoid aliases unless necessary.

I have run the above query without sum and grouping and I get a set of rows for each column I do a SELECT on, which I suspect is then multiplied or added together, but I was under the impression that grouping and/or doing sum(TABLE.COLUMN) would solve that.

Another thing is that, I think, doing a SELECT DISTINCT or any other DISTINCT operation won't work since that will leave out some ("duplicate") results.

PS. If it matters, my dev machine is a WAMP but release will be on ubuntu/apache/mysql/php.

Answer 1

To understand why you're not getting the answers you expect, take a look at this query:

SELECT * FROM player LEFT JOIN goal USING (idplayer)

As you can see, the rows on the left are duplicated for the matching rows on the right. That procedure is repeated for each join. Here's the raw data for your query:

SELECT * FROM player
LEFT JOIN goal USING (idplayer)
LEFT JOIN assist USING (idplayer)
LEFT JOIN gw USING (idplayer)
LEFT JOIN win USING (idplayer)
LEFT JOIN played USING (idplayer)

Those repeated values are then used for the SUM calculations. The SUMs need to be calculated before the rows are joined:

SELECT firstname, lastname, goals, assists, gws, wins, games_played
FROM player
INNER JOIN 
  (SELECT idplayer, SUM(goal) AS goals FROM goal GROUP BY idplayer) a
  USING (idplayer)
INNER JOIN
  (SELECT idplayer, SUM(assist) AS assists FROM assist GROUP BY idplayer) b
  USING (idplayer)
INNER JOIN
  (SELECT idplayer, SUM(gw) AS gws FROM gw GROUP BY idplayer) c
  USING (idplayer)
INNER JOIN
  (SELECT idplayer, SUM(win) AS wins FROM win GROUP BY idplayer) d
  USING (idplayer)
INNER JOIN
  (SELECT idplayer, COUNT(*) AS games_played FROM played GROUP BY idplayer) e
  USING (idplayer)

SQLFiddle