title: mysql Group by in nested query
body: <p>I have employee_id,status and payment field. I need to produce two different output ( sum(payment)) with two different condition. 
  The problem is when i use group by, it produce the total sum of all rows, whilst I want it to group by the employee id.</p>
  
  <p>this is my query:</p>
  
  <pre><code>select employee_id, 
  (select sum(payment) from tblempay where status=1) AS 'Total 1',
  (select sum(payment) from tblempay where status=2) AS 'Total 2'
  from tblempay
  group by employee_id
  </code></pre>
  
  <p>hope somebody can help me. Thank you.</p>