How do I show zero when I use count by using self left join?
To answer your question, I did the following:
Edit:
Sorry, I missed the bit about 0 modules failed for students who had never got a grade below 40 in any module - please see revised code below (note to self - read the question!). Luckily, the DDL and table insert DML remain the same.
I reworked your schema slightly as follows (fiddle here):
CREATE TABLE assessment 
(
  assessment_id INT, 
  mark INTGER, 
  student_id TEXT, 
  module_id TEXT, 
  PRIMARY KEY (assessment_id, student_id, module_id), 
  FOREIGN KEY (student_id) REFERENCES student(student_id), 
  FOREIGN KEY (module_id) REFERENCES module(module_id)
);

Populate it:
INSERT INTO assessment VALUES
(1, 10, 1, 'French1'), 
(2, 20, 2, 'French1'), 
(3, 50, 3, 'French1'), 
(4, 40, 1, 'English2'), 
(5, 60, 2, 'English2'), 
(6, 90, 3, 'English2'),
(7, 30, 1, 'Maths1'),
(8, 10, 2, 'Maths1'),
(9, 80, 3, 'Maths1');

OR (see fiddle here):
INSERT INTO assessment VALUES
(1, 10, 1, 'French1'), 
(1, 20, 2, 'French1'), 
(1, 50, 3, 'French1'), 
(2, 40, 1, 'English2'), 
(2, 60, 2, 'English2'), 
(2, 90, 3, 'English2'),
(3, 30, 1, 'Maths1'),
(3, 10, 2, 'Maths1'),
(3, 80, 3, 'Maths1');

I'd say assessment_id should be an INTEGER, but I know SQLite has a weird thing going on with data types!
And then run the query above:
SELECT
  COUNT (a2.module_id) AS &quot;Mod. cnt&quot;,
  CASE
    WHEN 
      MIN(a1.mark) &lt;= 40
    THEN
      COUNT(a2.module_id)
    ELSE
      0
  END AS &quot;Failed modules&quot;,
  ROUND(AVG (a1.mark), 2) AS &quot;Avg grade&quot;, 
  a2.student_id AS &quot;Student id&quot;
FROM assessment a1
  LEFT JOIN assessment a2  
  ON a2.assessment_id = a1.assessment_id 
  AND a2.student_id = a1.student_id 
  AND a2.module_id = a1.module_id 
GROUP BY a2.student_id 
HAVING avg(a1.mark) &lt; 40 OR (SELECT MIN(a1.mark) &gt;= 40)
ORDER BY a2.student_id, avg(a1.mark) DESC; -- ALL outer queries should have an ORDER BY!

Result (same for either set of data):
Mod. cnt    Failed modules  Avg grade   Student id
       3                 3      26.67            1
       3                 3         30            2
       3                 0      73.33            3

I suggest that you have a separate module table and that you JOIN using INTEGERs as your PRIMARY KEYs - AFAIK, it's faster than TEXT - the SQL may become a wee bit more complex, but it'll be worth it in the long run - normalisation! You may wish to look at the SQLite COALESCE function for students who haven't taken any assessments?
Using GROUP_CONCAT(), you can do the following (just last two lines of query shown - see fiddle here):
a2.student_id AS &quot;Student id&quot;,
GROUP_CONCAT(a2.module_id || ', ' ||  a2.mark,  ', ') AS &quot;Grades&quot;

Result:
Mod. cnt    Failed modules  Avg grade   Student id  Grades
       3                 3      26.67            1  French1, 10, English2, 40, Maths1, 30
       3                 3         30            2  French1, 20, English2, 60, Maths1, 10
       3                 0      73.33            3  French1, 50, English2, 90, Maths1, 80