I have a table called vw_results which holds the result profile for a particular student:
idNum |courseUnit | marks | Gpp | grade| id | semseterID | sessionName
06/021| 2 | 47 | 8 | B+ | 1 | 1 | 2010/11
06/021| 3 | 56 | 7 | C | 1 | 1 | 2010/11
. | 4 | 34 | 5 | C | 1 | 1 | 2010/11
. | 5 | 34 | 0 | F | 1 | 1 | 2010/11
. | 2 | 89 | 10 | A | 1 | 2 | 2010/11
. | 3 | 45 | 4 | D | 1 | 2 | 2010/11
. | 4 | 56 | 10 | C | 1 | 2 | 2010/11
. | 2 | 67 | 12 | B+ | 2 | 1 | 2011/12
. | 1 | 70 | 15 | A | 2 | 1 | 2011/12
. | 2 | 80 | 10 | A | 2 | 2 | 2011/12
. | 3 | 90 | 5 | A | 2 | 2 | 2011/12
I have a form that uses two select boxes named sessionID and semesterID and the form uses a GET method, part of the php code is
<?php
...
#To calculate the GPA
if(isset('chkresult')){
$query="SELECT SUM( vwr.courseUnit ) cummUnit, SUM( vwr.GPP ) cummGPP, (
SUM( vwr.GPP ) / SUM( vwr.courseUnit ))cummGPA
FROM vw_result vwr
WHERE vwr.Grade NOT IN ('F') AND vwr.sessionID=".$_GET['sessionID']." AND vwr.semesterID=".$_GET['semesterID'].";"
$result=mysql_query($query);
$row_query=mysql_fetch_assoc($result);
}
...
?>
supposing I want to calculate the CGPA cummmulatively such that when $_GET['sessionID']=1 and $_GET['semesterID']=2
<?php echo $row_query['cummGPA']; ?>
using the vw_result table, the result shoud be
|cummGPA|
|2.444 |
also if $_GET['sessionID']=2 and $_GET['semesterID']=1
then, the result should be:
|cummGPA|
|3.381 |
if $_GET['sessionID']=2 and $_GET['semesterID']=2
then
|cummGPA|
|3.301 |
any suggestions on what SQL statement can be used using the vw_result table above to get the answers of cummGPA as shown above?
THANKS!
