title: Sum Issue with multiple fields in mysql select query
body: <p>I have two tables rentals and sales.There is a field status.If status=1 is unpublish,status=2 is publish and status=3 is pending.</p>
  
  <p>I want to find sum of all publish,unpublish and pending in rentals and sales for agents.</p>
  
  <p>Here is what i tried but it give me wrong data</p>
  
  <pre><code>select sum(publish1) publish, sum(unpublish1) unpublish, sum(pending1) pending, agent_id,status from ( 
  
  select agent_id,status, count(*) as publish1, 0 unpublish1, 0 pending1 from rentals where status = 2  GROUP BY agent_id 
  
  union all select agent_id,status, 0 publish1, count(*) as unpublish1, 0 pending1 from rentals where status = 1  GROUP BY agent_id 
  
  union all select agent_id,status, 0 publish1, 0 pending1, count(*) as pending1 from rentals where status = 3 GROUP BY agent_id 
  
  union all select agent_id,status, count(*) as publish1, 0 unpublish1, 0 pending1 from sales where status = 2  GROUP BY agent_id 
  
  union all select agent_id,status, 0 publish1, count(*) as unpublish1, 0 pending1 from sales where status = 1 GROUP BY agent_id 
  
  union all select agent_id,status, 0 publish1, 0 pending1, count(*) as pending1 from sales where status = 3  GROUP BY agent_id ) s GROUP BY agent_id
  </code></pre>