title: GROUP BY and LEFT JOIN with COUNT from same table
body: <p>Want to count according to year, type and order by month</p>
  
  <p>This is my <code>user table</code>:</p>
  
  <pre><code>id  type  uname      date_created
  1    fac   a      2015-12-28 17:11:19
  2    cs    b      2015-12-23 19:09:51
  3    cs    c      2015-12-23 19:09:21
  4    stu   d      2015-12-31 18:12:41
  5    fac   e      2015-11-11 00:00:00
  6    fac   f      2015-10-07 00:00:00
  </code></pre>
  
  <p>Expected result:</p>
  
  <pre><code>fac  stu  cs
   1    1    2 // month january
   1    0    0 // month octomber
   1    0    0 // month november
  </code></pre>
  
  <p>What I tried is:</p>
  
  <pre><code>SELECT count(u1.id) as fac, count(u2.id) as stu, count(u3.id) as cs
  FROM user u
  left join user u1 ON u1.faculty = 'yes' AND YEAR(u1.date_created) = 2015
  left join user u2 ON u2.faculty = 'no' AND YEAR(u2.date_created) = 2015
  left join user u3 ON u3.faculty = 'club_student' AND YEAR(u3.date_created) = 2015
  GROUP BY MONTH(u.date_created) ORDER BY MONTH(u.date_created)
  </code></pre>
  
  <p>Giving me wrong result like:</p>
  
  <pre><code>fac  stu  cs
  6    6    6 
  6    6    6 
  24   24   24
  </code></pre>