title: In mysql how can I sum 2 columns in different tables and group by a common column
body: <p>I have two tables:</p>
  
  <p>Doctor</p>
  
  <pre><code>Headings wardName, x, y, docYears
  </code></pre>
  
  <p>Nurse</p>
  
  <pre><code>Headings wardName, a, b, nurseYear
  </code></pre>
  
  <p>There are several rows with the same wardName in each table. I want to sum all of the docYears+nurseYears for each wardName. At the moment I have this:</p>
  
  <pre><code>SELECT Doctor.wardName, SUM(docYears+nurseYears) AS Total 
  FROM Doctor 
  JOIN Nurse WHERE Doctor.wardName=Nurse.wardName 
  GROUP BY Doctor.wardName, Nurse.wardName;
  </code></pre>
  
  <p>And this effectively giving the sum(docYears)*number of rows nurses + sum(nurseYears)*number of rows of doctors for each ward. I don't want to have it multiplying by the number of rows in the other table as this gives the incorrect answer. Could anyone help me rectify this please?</p>