Solved Query to show number of student not pass the exam (1 Viewer)

Babycat

Member
Local time
Today, 19:58
Joined
Mar 31, 2020
Messages
275
Hi everyone

I have the tables and relationship showed as below.
I want to have query that shows number of student not pass (point <5) first round of exam per class

Can anyone please help me?

The db is attached.
It is prefer to not use more than 1 query.

1646126715265.png
 

Attachments

  • Student.zip
    28.3 KB · Views: 161

Eugene-LS

Registered User.
Local time
Today, 15:58
Joined
Dec 7, 2018
Messages
481
It is prefer to not use more than 1 query.
SQL:
SELECT
    ClassName,
    Sum(IIf([FirstRound_Point]<5,1,0)) AS [Total not pass]
FROM
    (CLASS LEFT JOIN STUDENT ON CLASS.ClassID = STUDENT.ClassID)
    LEFT JOIN RESULT ON STUDENT.StudentID = RESULT.StudentID
GROUP BY CLASS.ClassName
ORDER BY CLASS.ClassName;
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:58
Joined
Feb 19, 2013
Messages
16,555
create an aggregate query (group by) based on class, student and result

Code:
SELECT CLASS.ClassName, Count(CLASS.ClassID) AS CountOfClassID
FROM CLASS INNER JOIN (STUDENT INNER JOIN RESULT ON STUDENT.StudentID = RESULT.StudentID) ON CLASS.ClassID = STUDENT.ClassID
WHERE (((RESULT.FirstRound_Point)<5))
GROUP BY CLASS.ClassName;
 

Babycat

Member
Local time
Today, 19:58
Joined
Mar 31, 2020
Messages
275
Dear buddies

Thanks for your quick helps, but seem the results are not correct. The duplicated values are not exclusive.

As per manual check as below, ClassA has 3 students( A001, A007 and A008) while class B has 2 (A002 and A004)

1646130131434.png


Here is your results, they are incorrect

1646130280791.png


1646130295248.png
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:58
Joined
Feb 19, 2013
Messages
16,555
Always helps if you provide the outcome expected from the example data since both of us interpreted your requirement the same way

try this

Code:
SELECT CLASS.ClassName, Count(A.ClassID) AS CountOfClassID
FROM (SELECT STUDENT.ClassID, STUDENT.StudentID
FROM STUDENT INNER JOIN RESULT ON STUDENT.StudentID = RESULT.StudentID
WHERE (((RESULT.FirstRound_Point)<5))
GROUP BY STUDENT.ClassID, STUDENT.StudentID)  AS A INNER JOIN CLASS ON A.ClassID = CLASS.ClassID
GROUP BY CLASS.ClassName;
 

Babycat

Member
Local time
Today, 19:58
Joined
Mar 31, 2020
Messages
275
Well noted and thank you very much
It works like a charm
 

Users who are viewing this thread

Top Bottom