title: SQL - How can I JOIN two tables and SUM a column based on IDs between them?
body: <p>I have two tables. One table is with master data</p>
  
  <p><strong>Table tbl1:</strong></p>
  
  <pre><code>+-------------+------------+------------+
  | ID          | Name       | Total      | 
  +-------------+------------+------------+
  | 1           | a          | 10         |
  | 2           | b          | 5          |
  | 3           | c          | 4          |
  | 4           | a          | 4          |
  +-------------+------------+------------+
  </code></pre>
  
  <p>Second table <code>tbl2</code> contains child data. The key between tables is ID</p>
  
  <p><strong>Table tbl2:</strong></p>
  
  <pre><code>+-------------+------------+
  |id           | qty        | 
  +-------------+------------+
  | 1           | 4          |              
  | 1           | 3          |                
  | 1           | 1          |                
  | 3           | 1          |                
  | 3           | 3          |                
  +-------------+------------+
  </code></pre>
  
  <p>I need to get output like this:</p>
  
  <p><strong>Output:</strong></p>
  
  <pre><code>+-------------+------------+------------+
  | name        | sum_tot    | sum_qty    | 
  +-------------+------------+------------+
  | a           | 14         | 8          |
  | b           | 5          | 0          |
  | c           | 4          | 4          |
  +-------------+------------+------------+
  </code></pre>
  
  <p>I had tried with this:</p>
  
  <pre><code>select tbl1.name, SUM(tbl1.total), SUM(tbl2.qty)
  from tbl1
  left join tbl2 ON tbl1.id = tbl2.id
  GROUP by tbl1.name
  </code></pre>
  
  <p>The output that I get is:  </p>
  
  <p><strong>Output:</strong></p>
  
  <pre><code>+-------------+------------+------------+
  | name        | sum_tot    | sum_qty    | 
  +-------------+------------+------------+
  | a           | 34         | 8          |
  | b           | 5          |null        |
  | c           | 8          | 4          |
  +-------------+------------+------------+
  </code></pre>
  
  <p>Which is not correct.</p>
  
  <p>Here is the <a href="http://sqlfiddle.com/#!9/646b08/3" rel="nofollow noreferrer">sql fiddle</a>:</p>
  
  <p><a href="https://i.stack.imgur.com/zrZsf.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/zrZsf.png" alt="enter image description here"></a></p>
  
  <p>The summary from first table is not in relation with second table. It seems that somehow query runs three times.</p>