I have a query that must calculate the averages of students according to the id of the subject, the period and the year, at the moment it is like this:
SELECT calificaciones.id_materia,
Round(Avg (calificaciones.calificacion), 2) AS promedio,
materias.id,
materias.nombre_materia,
CASE
WHEN Round(Avg (calificaciones.calificacion), 2) = '10.0' THEN 'AU'
WHEN Round(Avg (calificaciones.calificacion), 2) >= '9.0'
AND Round(Avg (calificaciones.calificacion), 2) < '10.0' THEN 'DE'
WHEN Round(Avg (calificaciones.calificacion), 2) >= '8.0'
AND Round(Avg (calificaciones.calificacion), 2) < '9.0' THEN 'SA'
ELSE 'NA'
end AS 'EscalaA',
IF (calificaciones.calificacion < '8.0', 'Materia sin aprobar',
'Materia aprobada') AS estado
FROM calificaciones
INNER JOIN materias
ON calificaciones.id_materia = materias.id
WHERE calificaciones.calificacion_matricula = '179170'
AND calificaciones.periodo = '1'
AND calificaciones.año = '2022'
GROUP BY calificaciones.id_materia ASC
The problem I find is that if a student, for example from the subject with id=1, had a 10.0 in unit 1 and a 6.2 in unit 2, as seen below:
the estado
should be " Subject not approved " because it has units that it has not approved, but I understand that since the query finds a grade of 10.0 in unit 1 for that subject id, the condition of the is not met if
. The failed result is the following, where Mathematics corresponds to subject_id = 1:
I have tried several ways but I can't. Likewise, I don't know if it is possible that in cases like this where there are units without passing (with a grade lower than 8.0) instead of calculating the average EscalaA
it is directly 'NA', and that only in cases where all the units of a subject id are approved (with a grade >= 8.0) calculate the average. I appreciate your responses and comments.