title: Mysql query to get count per months
body: <p>I have a table for my users that have a field named "created" that have the registration date.</p>
  
  <p>How can i get a list that contains a count for the registrations number per month in last 12 months?
  Like this:</p>
  
  <pre><code>Month   Count
  1        1232
  2        2222
  3         122
  4        4653
  ...       ...
  12       7654
  </code></pre>
  
  <p>I'm not used to working with mysql, so until now i just know how to count the number of registrations in last year, not how to group that count by last 12 months. Thanks in advance! </p>
  
  <p>UPDATE</p>
  
  <p>Now I'm getting this, using @fthiella solution:</p>
  
  <pre><code>+------------------------------+-------------------------------+----------+
  | Year(FROM_UNIXTIME(created)) | Month(FROM_UNIXTIME(created)) | Count(*) |
  +------------------------------+-------------------------------+----------+
  |                         2012 |                             4 |     9927 |
  |                         2012 |                             5 |     5595 |
  |                         2012 |                             6 |     4431 |
  |                         2012 |                             7 |     3299 |
  |                         2012 |                             8 |      429 |
  |                         2012 |                            10 |     3698 |
  |                         2012 |                            11 |     6208 |
  |                         2012 |                            12 |     5142 |
  |                         2013 |                             1 |     1196 |
  |                         2013 |                             2 |       10 |
  +------------------------------+-------------------------------+----------+
  </code></pre>
  
  <p>How can i force query to give me the months with count = 0?</p>
  
  <p>Solution by @fthiella (thanks a lot!):</p>
  
  <pre><code>     SELECT y, m, Count(users.created)
       FROM (
        SELECT y, m
        FROM
           (SELECT YEAR(CURDATE()) y UNION ALL SELECT YEAR(CURDATE())-1) years,
           (SELECT 1 m UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
             UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
             UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) months) ym
         LEFT JOIN users
         ON ym.y = YEAR(FROM_UNIXTIME(users.created))
            AND ym.m = MONTH(FROM_UNIXTIME(users.created))
       WHERE
         (y=YEAR(CURDATE()) AND m&lt;=MONTH(CURDATE()))
         OR
         (y&lt;YEAR(CURDATE()) AND m&gt;MONTH(CURDATE()))
       GROUP BY y, m;
  </code></pre>
  
  <p>And the results:</p>
  
  <pre><code>+------+----+----------------------+
  | y    | m  | Count(users.created) |
  +------+----+----------------------+
  | 2012 |  5 |                 5595 |
  | 2012 |  6 |                 4431 |
  | 2012 |  7 |                 3299 |
  | 2012 |  8 |                  429 |
  | 2012 |  9 |                    0 |
  | 2012 | 10 |                 3698 |
  | 2012 | 11 |                 6208 |
  | 2012 | 12 |                 5142 |
  | 2013 |  1 |                 1196 |
  | 2013 |  2 |                   10 |
  | 2013 |  3 |                    0 |
  | 2013 |  4 |                    0 |
  +------+----+----------------------+
  </code></pre>