'MySQL filtering based on criteria

I have a query that seeks to get students that failed. A student fails when he/she ;

  1. Gets an average of less than 50% OR passes less than 5 subjects OR fails English Language

Below is the query

SELECT
        grades.grade_name as label,
        COUNT(*) AS value
    FROM
        term_averages
    RIGHT JOIN grades ON grades.id = term_averages.student_class
    WHERE
         term_averages.number_of_passed_subjects<5 AND  term_averages.student_average <50 XOR term_averages.passing_subject_status=0 AND  term_averages.term_id = 4  AND term_averages.student_stream =1
    GROUP BY
        term_averages.student_class

I want the query to select students only those that meet the above criteria. When I use the AND it brings students that meet all the criteria. Yet it can happen that a student can only meet one failure criteria e.g Get More than 50% average but fail English.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source