title: SQL Incorrect SUMS from multiple JOINS
body: <p>I'm trying to sum multiple tables using Joins and Sums in MySQL and not having much success.</p>
  
  <p><strong>My Tables (Unnecessary Columns Removed)</strong></p>
  
  <p><strong>Students</strong></p>
  
  <pre><code>idStudent   studentname   studentyear
  1           foobar        11
  2           barfoo        11
  3           thing         8
  </code></pre>
  
  <p><strong>Athletics_Results</strong>        </p>
  
  <pre><code>idResult   idStudent   points
  1          1           14
  2          1           11
  3          3           7
  4          2           9
  </code></pre>
  
  <p><strong>Team_Results</strong></p>
  
  <pre><code>idTeamResults   year   points
  1               11     9
  2               8      8
  3               7      14
  </code></pre>
  
  <p>So let me explain about the tables, because I admit they're poorly named and designed.</p>
  
  <p>Students holds the basic info about each student, including their year and name. Each student has a unique ID.</p>
  
  <p>Athletics_Results stores the results from athletics events. The idStudent column is a foreign key and relates to idStudent in the student column. So student foobar (idStudent 1) has scored 14 and 11 points in the example.</p>
  
  <p>Team_Results stores results from events that more than one student took part in. It just stores the year group and points.</p>
  
  <p><strong>The Aim</strong></p>
  
  <p>I want to be able to produce a sum of points for each year - combined from both athletics_results and team_results. EG:</p>
  
  <pre><code>year   points
  7      14     &lt;-- No results in a_r, just 14 points in t_r
  8      15     &lt;-- 7 points in a_r (idResult 4) and 8 in t_r
  11     43     &lt;-- 14, 11, 9 points in a_r and 9 in t_r
  </code></pre>
  
  <p><strong>What I've tried</strong>
  For testing purposes, I've not tried combining the a_r scores and t_r scores yet but left them as two columns so I can see what's going on.</p>
  
  <p>The first query I tried:</p>
  
  <pre><code>SELECT students.studentyear as syear, SUM(athletics_results.points) as score, SUM(team_results.points) as team_score
  FROM students
  JOIN team_results ON students.studentyear = team_results.year
  JOIN athletics_results ON students.idStudent = athletics_results.idStudent
  GROUP BY syear;
  </code></pre>
  
  <p>This gave different rows for each year (as desired) but had incorrect SUMS. I learnt this was due to not grouping the joins.</p>
  
  <p>I then created this code:</p>
  
  <pre><code>SELECT studentyear as sYear, teamPoints, AthleticsPoints
  FROM students st
  
  JOIN    (SELECT year, SUM(tm.points) as teamPoints
          FROM team_results tm
          GROUP BY year) tr ON st.studentyear = tr.year 
  
  JOIN    (SELECT idStudent, SUM(atr.points) as AthleticsPoints
          FROM athletics_results atr
          ) ar ON st.idStudent = ar.idStudent
  </code></pre>
  
  <p>Which gave correct SUMS but only returned one year group row (e.g the scores for Year 11).</p>
  
  <p>EDIT - SQLFiddle here: <a href="http://sqlfiddle.com/#!9/dbc16/" rel="nofollow">http://sqlfiddle.com/#!9/dbc16/</a>. This is with my actual test data which is a bigger sample than the data I posted here.</p>