title: SUM of a column from a second degree join?
body: <p>I'm trying to query 3 tables: <code>students</code>, <code>template_subjects</code>, and <code>subjects</code>.</p>
  
  <p>The <code>students</code> and <code>template_subjects</code> tables have a <code>template_code</code> column, so I can join them through this column (which I think is a pivot table between a <code>template_codes</code> table and the <code>subjects</code> table. This isn't a database I designed, just querying it now).</p>
  
  <p>Now, I need to get the sum of the <code>units</code> column from the <code>subjects</code> table grouped by the <code>template_code</code>. How can I do it?</p>
  
  <p>Here's what I've tried so far:</p>
  
  <pre><code>SELECT 
  s.studNo as 'Student Number',
  s.lastName as 'Student Name',
  s.template_code as 'Template Code',
  (SELECT sum(units) FROM subjects
  JOIN template_subjects ON subjects.subject_code=template_subjects.subject_code
  GROUP BY s.template_code) as 'Units'
  FROM students as s
  JOIN template_subjects ON template_subjects.template_code=s.template_code
  </code></pre>
  
  <p>What I expect to get from the query is, for example:</p>
  
  <pre><code>Student Number | Student Name | Template Code | Units
  201555101        Jane Doe       ACC301          35
  </code></pre>
  
  <p>Where <code>Units</code> is the total units of subjects inside a certain template code. (Table names and columns have much more complex names, so I simplified them in this question. Please inform me of any errors or confusing parts that I may have written in this question.)</p>