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.
I think you need to figure out what you're asking the DB for.
Theoretical framework
In a query with grouping you will have some columns whose value is exposed directly, and they are the grouping criteria, eg
What this query returns cannot have more than one record for each
id_materia
, because if it were, it would be appropriate to group them.Columns whose value is a function of a grouping field can necessarily be consolidated in the same record.
There is also no problem using aggregate joins, since the join of two functions is still a function. For example:
What happens when a field is not a grouping criterion and is not a function of them? Depending on the engine configuration, what is formally incorrect will be ignored. In many cases it is harmless. For example
nombre_materia
, it has a 1:1 relationship withid_materia
. But there are cases where it is not harmless, when tolerance overlooks an omission that leads to an inconsistent result.This field is not an aggregate, is not a function of,
id_materia
and is not a grouping field. At first glance, it would generate two records for each subject, and the DB will silently discard the second one because -with the best intentions- it tries to return only one record for each subject.When the DB client is configured in strict mode, however, the above bypass is considered an error.
Proposed solution
You have two options. Add the grade to the grouping criteria (which doesn't make sense) or rethink what you want to calculate. And here comes the core:
Is the rule that, in order to pass, you cannot have a grade below 8 in any unit? then use the minimum rating, which is an aggregate.
Additionally, although it is not relevant to the result, it is good to "prune" the irrelevant logic, because it does not contribute and only confuses you. In your case:
CASE...WHEN...END
.WHEN x >= 9 AND x<10...
can dispense withx<10
, because if it didn't enter the firstWHEN
then it's truism.id
, which is redundant.The query would be: