title: Using SUM with multiple joins in mysql
body: <p>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.</p>
  
  <p>First up, my questions/problem:</p>
  
  <ol>
  <li>I want to sum and count certain columns in a multiple join query</li>
  <li>Is it not possible with multiple joins? Do I have to nest <code>SELECT</code> queries?</li>
  </ol>
  
  <p>Here's a SQL dump of my database with sample data: <a href="http://pastie.org/private/vq7qkfer5mwyraudb5dh0a" rel="noreferrer">http://pastie.org/private/vq7qkfer5mwyraudb5dh0a</a></p>
  
  <p>This is the query I thought would do the trick:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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)</p>
  
  <pre><code>+-----------+----------+------+--------+----+-----+--------+
  | 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 |
  +-----------+----------+------+--------+----+-----+--------+
  </code></pre>
  
  <p>So, to iterate the above questions again, is this possible with one query and multiple joins?</p>
  
  <p>If you provide solutions/queries, <strong>please explain them!</strong> 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.</p>
  
  <p>I have run the above query without sum and grouping and I get a set of rows for each column I do a <code>SELECT</code> on, which I suspect is then multiplied or added together, but I was under the impression that grouping and/or doing <code>sum(TABLE.COLUMN)</code> would solve that.</p>
  
  <p>Another thing is that, I think, doing a <code>SELECT DISTINCT</code> or any other <code>DISTINCT</code> operation won't work since that will leave out some ("duplicate") results.</p>
  
  <p>PS. If it matters, my dev machine is a WAMP but release will be on ubuntu/apache/mysql/php.</p>