Hi,
I've been trying to add a column with the average grade of the students but I can't figure out how to do it.
The exams table contains 3 columns with the grades (calificacion1, calificacion2, calificacion3), but the tricky part is that not every student has all 3 grades (some grades are NULL), and if they are NULL then they shouldn't be counted.
What I have tried is:
1- At the model tab, I tried adding the fields I need plus the calculated one in the following way:
Fileds:
But the query gets completely mishmashed, here is the error:
2- Based in some posts I saw I tried also the following code (for simplicity I removed the id and the name of the student, until I find a working solution):
Fileds:
But the error is similar:
3- So I tried by calculating the average by using the PHP Functions at the Front List Settings but also didn't work:
PHP Functions:
but the average column is empty, I did enable error reporting in my .htaccess but I don't see any error messages.
Any suggestions would be greatly appreciated.
Thanks an advance
I've been trying to add a column with the average grade of the students but I can't figure out how to do it.
The exams table contains 3 columns with the grades (calificacion1, calificacion2, calificacion3), but the tricky part is that not every student has all 3 grades (some grades are NULL), and if they are NULL then they shouldn't be counted.
What I have tried is:
1- At the model tab, I tried adding the fields I need plus the calculated one in the following way:
Fileds:
`examen.cf_id`, `examen.estudiante`, `examen.fecha`,(SUM(ifnull(calificacion1,0))+SUM(ifnull(calificacion2,0))+SUM(ifnull(calificacion3,0)) ) / (COUNT(calificacion1)+Count(calificacion2)+COUNT(calificacion3)) as `examen.promedio`
But the query gets completely mishmashed, here is the error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')) ) / (`examen`.`COUNT(calificacion1`)+Count(calificacion2)+COUNT(calific' at line 1 SQL=SELECT `examen`.`cf_id` AS `examen.cf_id`, `examen`.`estudiante` AS `examen.estudiante`, `examen`.`fecha` AS `examen.fecha`, `examen`.`(SUM(ifnull(calificacion1` AS `examen.(SUM(ifnull(calificacion1`, `trabajolibre`.`0))+SUM(ifnull(calificacion2` AS `trabajolibre.0))+SUM(ifnull(calificacion2`, `trabajolibre`.`0))+SUM(ifnull(calificacion3` AS `examen.0))+SUM(ifnull(calificacion3`, 0)) ) / (`examen`.`COUNT(calificacion1`)+Count(calificacion2)+COUNT(calificacion3)) as `examen.promedio` AS `examen.COUNT(calificacion1` FROM `jos_chronoforms_data_examen` AS `examen` LIMIT 30 OFFSET 180
2- Based in some posts I saw I tried also the following code (for simplicity I removed the id and the name of the student, until I find a working solution):
Fileds:
<?php return array( "((SUM(ifnull(calificacion1,0))+SUM(ifnull(calificacion2,0))+SUM(ifnull(calificacion3,0)) ) / (COUNT(calificacion1)+Count(calificacion2)+COUNT(calificacion3)))" => "examen.promedio");
But the error is similar:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')) ) / (`examen`.`COUNT(calificacion1`)+Count(calificacion2)+COUNT(calific' at line 1 SQL=SELECT `examen`.` "examen.promedio"); AS `examen.COUNT(calificacion1` FROM `jos_chronoforms_data_examen` AS `examen` LIMIT 30 OFFSET 180
3- So I tried by calculating the average by using the PHP Functions at the Front List Settings but also didn't work:
PHP Functions:
examen.promedio: {if( is_null($row["examen"]["calificacion1"])){$divisor = 0; $firstCalif = 0;} else {$divisor = 1; $firstCalif = $row["examen"]["calificacion1"];}; if ( is_null($row["examen"]["calificacion2"])) {$divisor = $divisor; $secondCalif = 0;} else {$divisor++; $secondCalif = $row["examen"]["calificacion2"] ;} if ( is_null($row["examen"]["calificacion1"])){ $divisor= $divisor; $thirdCalif = 0; } else { $divisor++; $thirdCalif = $row["examen"]["calificacion3"] ;} $promedio = ($firstCalif + $secondCalif + $thirdCalif) / $divisor; return $promedio};
but the average column is empty, I did enable error reporting in my .htaccess but I don't see any error messages.
Any suggestions would be greatly appreciated.
Thanks an advance