title: MySQL WEEK() : Get all weeks in date range (with/without records)
body: <p>Here's an sql query I am using to get count of records in a table separated by week (only date is stored in table). It works as expected. </p>
  
  <pre><code>SELECT count(id), CONCAT('Week ',WEEK(complaintRaisedDate)) week
  FROM events
  WHERE categoryId=1
  GROUP BY week
  ORDER BY week
  </code></pre>
  
  <p>This yields result like</p>
  
  <pre><code>count(id)       week
  ---------- | ----------
  
  1               Week 36
  2               Week 40
  1               Week 41
  </code></pre>
  
  <p>How I want the result to be is below:</p>
  
  <pre><code>count(id)       week
  ---------- | ----------
  1               Week 36
  0               Week 37
  0               Week 38
  0               Week 39
  2               Week 40
  1               Week 41
  </code></pre>
  
  <p>That is, if no records found for a particular week it should still show the week (within the date range of records in table) with a count of 0. I can figure out a way to do this in PHP, but I was wondering if it can be achieved with a little tweaking of the SQL query itself. Is it possible? Thanks.</p>
  
  <p>Edit: <a href="http://sqlfiddle.com/#!2/3aa83/1" rel="nofollow">SQLFiddle</a></p>