sqlite – How do I show zero when I use count by using self left join?

I am trying to use count() to count the number of the module where the particular student got the average mark below 40. It also needs to show the number 0 if the student does not have any assessment mark lower than 40. However, my query does not show any 0 number at all.

CREATE TABLE assessment (
assessmentId text, 
mark integer, 
studentId text, 
moduleId text, 
PRIMARY KEY (assessmentId, studentId, moduleId), 
FOREIGN KEY(studentId) REFERENCES student(studentId), 
FOREIGN KEY(moduleId) REFERENCES module(moduleId)) ";

in each record, it shows the module that the assessment is from, and show which student took the assessment.

this is my query

 "select count(m.moduleId), avg(e.mark), m.studentId 
from assessment e 
left join assessment m on m.assessmentId = e.assessmentId 
and m.studentId = e.studentId 
and m.moduleId = e.moduleId 
group by m.studentId 
having avg(e.mark)<40";

the module could have more than one assessment, therefore the avg(e.mark) is to find out the average mark for each module for each student. Therefore it is group by studentId

so what i want the query to show is:


studentId|avg(e.mark)|count(m.moduleId)
1        | 50        | 0
2        | 20        | 2

However I am getting the result below


studentId|avg(e.mark)|count(m.moduleId)
1        | 20        | 2


I really don’t know where the problem is