title: MYSQL Union and combine amounts from duplicate rows
body: <p>I am trying to combine results from two different set of tables into one result.  Here is my query so far:</p>
  
  <pre><code>SELECT * 
  FROM ( SELECT product_table1.product_id as product_id, sum(product_table1.qty) as quantity, sum(product_table1.paid) as amount  
  FROM product_table1
  LEFT JOIN table2 ON table2.id = product_table1.table2_id
  WHERE product_table1.product_id IN ( SELECT id FROM products_table WHERE active = 'yes' )
  AND table2.active = 'yes'
  GROUP BY product_id
  
  UNION 
  
  SELECT product_table2.product_id as product_id, sum(product_table2.qty) as quantity, sum(product_table2.paid) as amount 
  FROM product_table2
  LEFT JOIN table3 ON table3.id = product_table2.table3_id
  WHERE product_table2.product_id IN ( SELECT id FROM products_table WHERE active = 'yes' )
  AND table3.active = 'yes'
  GROUP BY product_id
  
  ) AS product_sales
  </code></pre>
  
  <p>And here is how the data is coming back:</p>
  
  <pre><code>product_id | quantity | amount
  1            100        200
  2            200        300
  3            300        600
  1            500        700
  4            200        200
  </code></pre>
  
  <p>I am trying to figure out how best to take these two set of numbers (each having the same amount of columns and data types), and combining them into one set of results without having to bring php into it.  Is this possible?</p>
  
  <p>I found some other solutions but am not sure if they match what I am trying to do. <a href="https://stackoverflow.com/questions/6784969/mysql-union-query-duplicate-group-by-problem">Example of another solution</a> that I think is what I need, but not to sure..</p>