title: MySQL Select multiple count in one query
body: <p>I have 2 tables - <code>departments</code> and <code>employees</code>. I want to select all columns from <code>departments</code> and also add a column with number of subdepartments and number of employees in each department.</p>
  
  <p>Simplified tables look like this:</p>
  
  <p><code>departments</code>:</p>
  
  <pre><code>id      name            parent_id
  ---------------------------------
  1       IT              NULL
  2       HR              NULL
  3       Bussiness       NULL
  4       Web dev         1
  5       Ecommerce       4
  6       Advertisement   3
  7       Control         3
  8       Programmers     1
  </code></pre>
  
  <p><code>employees</code>:</p>
  
  <pre><code>id      name            department_id
  ---------------------------------
  1       Adam            1
  2       Ben             8
  3       Charles         7
  4       David           4
  5       Eugen           4
  6       Frank           6
  7       Gustav          6
  8       Heremy          4
  9       Igor            5
  10      Jacob           3
  </code></pre>
  
  <p>Result I want is:</p>
  
  <pre><code>id      name            parent_id       department_count      employee_count
  ----------------------------------------------------------------------------
  1       IT              NULL            2                     1
  2       HR              NULL            0                     0
  3       Bussiness       NULL            2                     1
  4       Web dev         1               1                     3
  5       Ecommerce       4               0                     1
  6       Advertisement   3               0                     2
  7       Control         3               0                     1
  8       Programmers     1               0                     1
  </code></pre>
  
  <p>Here is my query, but it is not working properly. But if I remove 1 of the <code>COUNT</code>, the numbers are then correct. I think that the <code>NULL</code> values are the problem, but I don't know the solution.</p>
  
  <pre><code>SELECT d.*, COUNT(d2.parent_id) AS department_count, COUNT(e.id) AS employee_count
  FROM department AS d
  LEFT JOIN department AS d2 ON (d.id = d2.parent_id)
  LEFT JOIN employees AS z ON (e.department_id = d.id)
  GROUP BY d.id
  ORDER BY d.id
  </code></pre>
  
  <p>Any improvement on my code is welcome.</p>