title: Group by and aggregate functions
body: <p>I'm trying to use union and group by with aggregate functions, but all attempts either result in missing rows or incorrect data. Below are two queries that produce the data I need, now i just need to unite them</p>
  
  <p>Here is a sample SQL Dump on <a href="http://pastebin.com/yds4ZpCP" rel="nofollow">pastebin</a> </p>
  
  <p>SQL Query #1:</p>
  
  <pre><code>SELECT o.id as order_id, SUM(price * quantity) as total_prod, 0 as total_inst
  FROM orders as o, orders_product_line as opl, products as p
  WHERE opl.order_id = o.id
  AND p.id = opl.product_id
  GROUP BY o.id
  </code></pre>
  
  <p>Result for Query #1</p>
  
  <pre><code>+----------+------------+------------+
  | order_id | total_prod | total_inst |
  +----------+------------+------------+
  |        1 |       4200 |          0 |
  |        2 |      40000 |          0 |
  |        3 |    3600000 |          0 |
  |        4 |      44500 |          0 |
  |        5 |    1229800 |          0 |
  |        6 |   45000000 |          0 |
  +----------+------------+------------+
  </code></pre>
  
  <p>SQL Query #2:</p>
  
  <pre><code>SELECT o.id as order_id, 0 as total_prod, SUM(rate * hours) as total_inst
  FROM orders as o, orders_installation_line as oil, installations as i
  WHERE oil.order_id = o.id
  AND i.id = oil.intallation_id
  GROUP BY order_id
  </code></pre>
  
  <p>Result for Query #2:</p>
  
  <pre><code>+----------+------------+------------+
  | order_id | total_prod | total_inst |
  +----------+------------+------------+
  |        1 |          0 |       4675 |
  |        2 |          0 |     255000 |
  |        3 |          0 |      18880 |
  |        4 |          0 |        600 |
  |        5 |          0 |       3540 |
  +----------+------------+------------+
  </code></pre>
  
  <p>Here is my attempt at using union for the two tables</p>
  
  <pre><code>SELECT o.id as order_id, SUM(price * quantity) as total_prod, 0 as total_inst FROM orders as o, orders_product_line as opl, products as p WHERE opl.order_id = o.id AND p.id = opl.product_id GROUP BY o.id
  UNION ALL
  SELECT o.id as order_id, 0 as total_prod, SUM(rate * hours) as total_inst FROM orders as o,  orders_installation_line as oil, installations as i WHERE oil.order_id = o.id AND i.id = oil.intallation_id GROUP BY order_id
  </code></pre>
  
  <p>And the result</p>
  
  <pre><code>+----------+------------+------------+
  | order_id | total_prod | total_inst |
  +----------+------------+------------+
  |        1 |       4200 |          0 |
  |        2 |      40000 |          0 |
  |        3 |    3600000 |          0 |
  |        4 |      44500 |          0 |
  |        5 |    1229800 |          0 |
  |        6 |   45000000 |          0 |
  |        1 |          0 |       4675 |
  |        2 |          0 |     255000 |
  |        3 |          0 |      18880 |
  |        4 |          0 |        600 |
  |        5 |          0 |       3540 |
  +----------+------------+------------+
  </code></pre>
  
  <p>Lastly, Here is my attempt at using union after reading other answers on stack overflow:</p>
  
  <pre><code>SELECT *
  FROM 
  (
  SELECT o.id as order_id, SUM(price * quantity) as total_prod, 0 as total_inst
  FROM orders as o, orders_product_line as opl, products as p
  WHERE opl.order_id = o.id
  AND p.id = opl.product_id
  GROUP BY o.id
  
  UNION ALL
  
  SELECT o.id as order_id, 0 as total_prod, SUM(rate * hours) as total_inst
  FROM orders as o, orders_installation_line as oil, installations as i
  WHERE oil.order_id = o.id
  AND i.id = oil.intallation_id
  GROUP BY order_id
  ) Q
  GROUP BY Q.order_id
  </code></pre>
  
  <p>And the result of this last union:</p>
  
  <pre><code>+----------+------------+------------+
  | order_id | total_prod | total_inst |
  +----------+------------+------------+
  |        1 |       4200 |          0 |
  |        2 |      40000 |          0 |
  |        3 |    3600000 |          0 |
  |        4 |      44500 |          0 |
  |        5 |    1229800 |          0 |
  |        6 |   45000000 |          0 |
  +----------+------------+------------+
  </code></pre>
  
  <p>What am I missing? I need that last column <code>total_inst</code> to show values. This is the result I am looking for:</p>
  
  <pre><code>+----------+------------+------------+
  | order_id | total_prod | total_inst |
  +----------+------------+------------+
  |        1 |       4200 |       4675 |
  |        2 |      40000 |     255000 |
  |        3 |    3600000 |      18880 |
  |        4 |      44500 |        600 |
  |        5 |    1229800 |       3540 |
  |        6 |   45000000 |          0 |
  +----------+------------+------------+
  </code></pre>