title: Count as Zero if Row does not exist using group by with LEFT JOIN
body: <p>I am practicing with mysql and stuck in one scenario as below:</p>
  
  <p>I have two tables as below .</p>
  
  <pre><code> Table 1 :
      Distinct_dates having DATE as my_date column name:
  
      Table 2:my_records having following fields
  
      record_id,
      record_submitter_name,
      record_submit_date as  DATETIME
  
      I need to get the records submitted by each submitter for each unique month and it should return 0 if no record found for a month. 
  
      For example: the below query gives me distinct months from distinct_dates table.
      select distinct(DATE_FORMAT(my_date,'%m-%Y')) from distinct_dates;
      +--------------------------------+
      | (DATE_FORMAT(my_date,'%m-%Y')) |
      +--------------------------------+
      | 01-2016                        |
      | 01-2017                        |
      | 02-2017                        |
      | 03-2017                        |
      +--------------------------------+
      4 rows in set (0.00 sec)
  
  I am able to get the count of Records in each month by submitter and not getting Zero if No Record found for the particular month? 
  
  select distinct(DATE_FORMAT(record_submit_date,'%m-%Y')),count(record_id),record_submitter_name from my_records group by DATE_FORMAT(record_submit_date,'%m-%y'),records_submitter_name;
  
  I have basic understanding of JOINS but not able to get the correct values ,Any help or pointer will be appreciable. 
  </code></pre>
  
  <p>I need to get the count of Records by each record_submitter_name for each distinct month and it should give Zero if no record submitted by user in a particular month ?</p>
  
  <p>Further working with LEFT Join I am able to get the NULL value for the distinct dates for which data does not exist for the month , below is what i have queried.</p>
  
  <pre><code>select distinct(DATE_FORMAT(d.my_date,'%m-%Y')),count(m.record_id), m.record_submitter_name from distinct_dates d left join my_records m on DATE_FORMAT(m.record_submit_date,'%m-%Y') = DATE_FORMAT(d.my_date,'%m-%Y') and record_submitter_name='xyz' group by d.my_date;
  
  +----------------------------------+----------------+-------------------+
  | (DATE_FORMAT(d.my_date,'%m-%Y')) | count(m.record_id) | record_submitter_name |
  +----------------------------------+----------------+-------------------+
  | 01-2016                          |              1 | xyz               |
  | 01-2017                          |              1 | xyz               |
  | 02-2017                          |              0 | NULL              |
  | 03-2017                          |              0 | NULL              |
  +----------------------------------+----------------+-------------------+
  </code></pre>
  
  <p>But It is not working when using group by clause :
  Using Group By close , I have not received the required result:</p>
  
  <pre><code>select distinct(DATE_FORMAT(d.my_date,'%m-%Y')),count(m.record_id), m.record_submitter_name from distinct_dates d left join my_records m on DATE_FORMAT(m.record_submit_date,'%m-%Y') = DATE_FORMAT(d.my_date,'%m-%Y') group by d.my_date,record_submitter_name;
  
  Following NULL results was not in the output ....
      .
        +----------------------------------+----------------+-------------------+
          | (DATE_FORMAT(d.my_date,'%m-%Y')) | count(m.record_id) | record_submitter_name |
          +----------------------------------+----------------+-------------------+
          | 01-2016                          |              1 | xyz               |
          | 01-2017                          |              1 | xyz               |
          | 02-2017                          |              0 | NULL              |
          | 03-2017                          |              0 | NULL              |
          +----------------------------------+----------------+-------------------+
  </code></pre>
  
  <p>Is there something wrong with group by clause with LEFT Join ?</p>
  
  <p>Thanks</p>