title: Mysql count group by and left join not working as expected
body: <p>Below is a simple query for a click tracker.  I've had a look at a lot of other posts and I'm scratching my head.  I cannot get this query to work so that all rows from the calendar table (one calendar day per row) are displayed:</p>
  
  <pre><code>SELECT DATE_FORMAT(calendar_date, '%a %D') AS calendar_date, 
  count( tracker_id ) as clicks
  FROM calendar
  LEFT JOIN offer_tracker USING(calendar_id)
  WHERE 
    calendar_month = Month(CURDATE()) AND 
    calendar_year = Year(CURDATE()) AND ( offer_id = 4 OR offer_id IS NULL )
  GROUP BY calendar_date;
  </code></pre>
  
  <p>It's nearly there but not all rows in the calendar table are returned i.e. there is no Fri 2nd, Tue 6th, Wed 7th etc:  </p>
  
  <p><img src="https://i.stack.imgur.com/qVOcx.jpg" alt="result set"></p>
  
  <p>Does anyone have any ideas on where I'm going wrong?  Should I be using a subquery?</p>