[Solved] Query for students passed or not passed in specific Subjects [closed]

by


Without seeing the data, lets make some assumptions:

  1. A pass is awarded for a subject if the marks obtained for that subject are equal to or more than 50% of the total marks available for that subject.
  2. The name of the table is called Enrollment

To return a list of students who have passed at least 3 subjects we can use a query similar to the following:

This solution uses CASE to evaluate a 1 for a pass and a 0 for fail for each subject, then we sum those results and only return rows that have a score of 3 or more.

SELECT ID, Student_Name, Roll_Number
FROM Enrollment
WHERE 
  ( CASE WHEN (Subject1MarksObtained / Subject1TotalMarks) >= 0.5 THEN 1 ELSE 0 END
  + CASE WHEN (Subject2MarksObtained / Subject2TotalMarks) >= 0.5 THEN 1 ELSE 0 END
  + CASE WHEN (Subject3MarksObtained / Subject3TotalMarks) >= 0.5 THEN 1 ELSE 0 END
  + CASE WHEN (Subject4MarksObtained / Subject4TotalMarks) >= 0.5 THEN 1 ELSE 0 END
  ) >= 3

There are different way to approach this, but this query is simple to read and gets the job done.

If you are querying an access table, then CASE WHEN is not supported but you can use IIF or SWITCH to achieve the same results:

SELECT ID, Student_Name, Roll_Number
FROM Enrollment
WHERE 
  ( IIF( (Subject1MarksObtained / Subject1TotalMarks) >= 0.5, 1, 0)
  + IIF( (Subject2MarksObtained / Subject2TotalMarks) >= 0.5, 1, 0)
  + IIF( (Subject3MarksObtained / Subject3TotalMarks) >= 0.5, 1, 0)
  + IIF( (Subject4MarksObtained / Subject4TotalMarks) >= 0.5, 1, 0)
  ) >= 3

5

solved Query for students passed or not passed in specific Subjects [closed]