Count as Zero if Row does not exist using group by with LEFT JOIN

Question

I am practicing with mysql and stuck in one scenario as below:

I have two tables as below .

 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. 

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 ?

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.

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              |
+----------------------------------+----------------+-------------------+

But It is not working when using group by clause : Using Group By close , I have not received the required result:

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              |
        +----------------------------------+----------------+-------------------+

Is there something wrong with group by clause with LEFT Join ?

Thanks

Answer 1

I created another Employee master table as suggested by Sunjit and the following query is working as required.

SELECT distinct(DATE_FORMAT(A.my_date,'%m-%Y')),B.EMP_NAME ,COUNT(record_id)AS record_count FROM distinct_dates A JOIN employees B ON 1=1 LEFT JOIN my_records C ON B.emp_name=C.record_submitter_name AND DATE_FORMAT(A.my_date,'%m-%Y')=DATE_FORMAT(C.record_submit_d‌​ate,'%m-%Y') GROUP BY A.my_date,'%m-%Y',B.emp_name; 

Answer 2

You need two master tables the Date Master & another is Employee Master and create a cross product of the two tables where you will get Month & Year wise all the employees entries.

Here i am having two tables TABLE_A for date master & TABLE_B as employee master

Create the cross product: SELECT DISTINCT A.MONTH,A.YEAR,A.MONTH_YEAR,B.EMP_ID,B.EMP_NAME FROM TABLE_A A JOIN TABLE_B B ON 1=1

Now perform the left join on the table where you need to make the count of records for that employee.

SELECT DISTINCT A.MONTH,A.YEAR,A.MONTH_YEAR,B.EMP_ID,B.EMP_NAME 
,COUNT(C.ASSESMENT)AS COUNTS
FROM TABLE_A A
JOIN TABLE_B B
ON 1=1
LEFT JOIN TABLE_C C
ON B.EMP_ID=C.EMP_ID
AND A.MONTH_YEAR=C.MONTH_YEAR
GROUP BY A.MONTH,A.YEAR,A.MONTH_YEAR,B.EMP_ID,B.EMP_NAME 
--ORDER BY EMP_ID,YEAR,MONTH

The result will be like :- Result set

Answer 3

Use left join,

You should create a master table which contains all the dates.

Say your records are from 1900 then that table should have 1900-01-01 to 2100-01-01 record.

and then,

select 
    distinct(DATE_FORMAT(m.record_submit_date,'%m-%Y')),count(m.record_id),m.record_submitter_name 
from 
    dates d 
    left outer join 
    my_records m on DATE_FORMAT(m.record_submit_date,'%m-%Y') = d.date 
group by d.date,m.records_submitter_name;