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
Best Answer
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):
Populate it:
OR (see fiddle here):
I'd say
assessment_id
should be anINTEGER
, but I know SQLite has a weird thing going on with data types!And then run the query above:
Result (same for either set of data):
I suggest that you have a separate
module
table and that youJOIN
usingINTEGER
s as yourPRIMARY KEY
s - AFAIK, it's faster thanTEXT
- 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 SQLiteCOALESCE
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):Result: