mysql - Count and group by date should return 0 on no values -

i'm trying report number of interviews did per day.

so have table of interviews such as

interviewid,staffid,date,comments...

and date reference table containing date 2005 2020. having single date field named ref.

my query :

select count(*) cnt `interviews`  right join `dateref` on `date` = ref type = 2  , date > date_sub(now(),interval 7 day) group date_format(ref,'%y-%m-%d') 

is works fine display interview did not when did not interviews...

for example returns :

1 2 4 

but should return

0 1 0 2 0 4 0 

edit:

apparently problems comes clause because if remove it, query works fine...

try replace to

right join 

instead

left join 

another change is

where type = 2  

to

where type = 2 or type null 

so final query

select count(*) cnt `interviews`  right join `dateref` on `date` = ref (type = 2 or type null) , date > date_sub(now(),interval 7 day) group date_format(ref,'%y-%m-%d')