title: Generate report by month and year in mysql
body: <p>I need to generate a report 12 months before the current date.</p>
  
  <p>Today is 2014-04-29. So my date range would be 2013-04-29 to 2014-04-29.</p>
  
  <p>These is my sample data:</p>
  
  <pre><code>--------------------------------------
  -- user_id | lead_id | date_entered --
  --       1 |       1 | 2013-12-05   --
  --       1 |       2 | 2014-03-15   --
  --       1 |       3 | 2014-04-24   --
  --------------------------------------
  </code></pre>
  
  <p>Expected output should be:</p>
  
  <pre><code>--------------------------
  -- month | year | total --
  --   Apr | 2013 |     0 --
  --   May | 2013 |     0 --
  --   Jun | 2013 |     0 --
  --   Jul | 2013 |     0 --
  --   Aug | 2013 |     0 --
  --   Sep | 2013 |     0 --
  --   Oct | 2013 |     0 --
  --   Nov | 2013 |     0 --
  --   Dec | 2013 |     1 --
  --   Jan | 2014 |     0 --
  --   Feb | 2014 |     0 --
  --   Mar | 2014 |     1 --
  --   Apr | 2014 |     1 --
  --------------------------
  </code></pre>
  
  <p>Here is my query:</p>
  
  <pre><code>select a.month, a.year, IFNULL(d.total, 0) AS total
  from (
      SELECT 'Apr' month, 2013 year, 1 monthOrder UNION 
      SELECT 'May' month, 2013 year, 2 monthOrder UNION 
      SELECT 'Jun' month, 2013 year, 3 monthOrder UNION 
      SELECT 'Jul' month, 2013 year, 4 monthOrder UNION 
      SELECT 'Aug' month, 2013 year, 5 monthOrder UNION 
      SELECT 'Sep' month, 2013 year, 6 monthOrder UNION 
      SELECT 'Oct' month, 2013 year, 7 monthOrder UNION 
      SELECT 'Nov' month, 2013 year, 8 monthOrder UNION 
      SELECT 'Dec' month, 2013 year, 9 monthOrder UNION 
      SELECT 'Jan' month, 2014 year, 10 monthOrder UNION 
      SELECT 'Feb' month, 2014 year, 11 monthOrder UNION 
      SELECT 'Mar' month, 2014 year, 12 monthOrder UNION 
      SELECT 'Apr' month, 2014 year, 13 monthOrder
  ) AS a left join (
      SELECT date_entered, count(id) AS total 
      FROM leads AS b 
      WHERE user_id = 1 
      AND is_deleted = 0 
      AND date_entered BETWEEN "2013-04-29" AND "2014-04-29" 
      GROUP BY YEAR(b.date_entered), MONTH(b.date_entered)
  ) AS d on a.month = DATE_FORMAT(d.date_entered, "%b") 
  ORDER BY a.monthOrder asc
  </code></pre>
  
  <p>My query's output:</p>
  
  <pre><code>--------------------------
  -- month | year | total --
  --   Apr | 2013 |     1 --
  --   May | 2013 |     0 --
  --   Jun | 2013 |     0 --
  --   Jul | 2013 |     0 --
  --   Aug | 2013 |     0 --
  --   Sep | 2013 |     0 --
  --   Oct | 2013 |     0 --
  --   Nov | 2013 |     0 --
  --   Dec | 2013 |     1 --
  --   Jan | 2014 |     0 --
  --   Feb | 2014 |     0 --
  --   Mar | 2014 |     1 --
  --   Apr | 2014 |     1 --
  --------------------------
  </code></pre>
  
  <p>Because on my <code>on a.month = DATE_FORMAT(d.date_entered, "%b")</code> I compare the month that's why <code>Apr 2013 has a total 1</code>. How do I fix this issue? Can someone help me?</p>
  
  <p>is there a way I can compare both month and year? How do I do that?</p>
  
  <p>Thanks</p>