Using SQL to make calculation based on some given criteria

Question

In one of the examination applications I built, I am yet to figure out on a way to come up with an SQL statement to make this task possible. Our country's grading system requires only 7 subjects to be counted. In those subjects, all languages are compulsory(labelled lang in the pictorial demonstration). 2 of the best sciences are counted(labelled sci). One humanity(labelled hum). The 7th subject may be a technical subject(labelled tech), a science or a humanity depending on which has the best marks. For example, the student in the given table would have marks from MAT,ENG,KISW,PHY,CHEM,HIST and BST counted. I wrote PHP code utilizing arrays and it's taking up just too much memory and time which makes me fear it will bring the server to a halt. Take a look at the code below,

function calculatePointsB($adm,$term,$class,$year,$exam){
global $db;

$points=array(
    "A"=>12,
    "A-"=>11,
    "B+"=>10,
    "B"=>9,
    "B-"=>8,
    "C+"=>7,
    "C"=>6,
    "C-"=>5,
    "D+"=>4,
    "D"=>3,
    "D-"=>2,
    "E"=>1
);
$choices=array();
$countable=array();
$monitor=array();
$grades=array();

$common_query_all=$db->prepare("SELECT marks,subject,sub_cat FROM averaged_marks WHERE adm_no=? AND sub_cat=? AND term=? AND class=? AND year=?");
$common_query_sp=$db->prepare("SELECT marks,subject,sub_cat FROM exmarks WHERE adm_no=? AND sub_cat=? AND term=? AND class=? AND year=? AND e_type=?");
//languages first
$langs=array();
if($exam=="All") {
    $getlang = $common_query_all;
    $getlang->execute(array($adm, "lang", $term, $class, $year));
}else{
    $getlang = $common_query_sp;
    $getlang->execute(array($adm, "lang", $term, $class, $year,$exam));
}
while($rst=$getlang->fetch(PDO::FETCH_ASSOC)){
    if($rst['marks']!=null) {
        $langs[$rst['subject']] = $rst['marks'];
        $grades[$rst['subject']]=$points[$rst['grade']];
        $countable[$rst['subject']] = $rst['marks'];
        array_push($monitor,$rst['sub_cat']);
    }else continue;
} unset($rst);

//sciences
$sciences=array();
if($exam=="All") {
    $getlang = $common_query_all;
    $getlang->execute(array($adm, "sci", $term, $class, $year));
}else{
    $getlang = $common_query_sp;
    $getlang->execute(array($adm, "sci", $term, $class, $year,$exam));
}
while($rst=$getlang->fetch(PDO::FETCH_ASSOC)){
    if($rst['marks']!=null) {
        $sciences[$rst['subject']] = $rst['marks'];
        array_push($monitor,$rst['sub_cat']);
    }else continue;
}unset($rst);
arsort($sciences);

//humanities
$humanities=array();
if($exam=="All") {
    $getlang = $common_query_all;
    $getlang->execute(array($adm, "hum", $term, $class, $year));
}else{
    $getlang = $common_query_sp;
    $getlang->execute(array($adm, "hum", $term, $class, $year,$exam));
}
while($rst=$getlang->fetch(PDO::FETCH_ASSOC)){
    if($rst['marks']!=null) {
        $humanities[$rst['subject']] = $rst['marks'];
        array_push($monitor,$rst['sub_cat']);
    }else continue;
}unset($rst);
arsort($humanities);

//technicals
$technic=array();
if($exam=="All") {
    $getlang = $common_query_all;
    $getlang->execute(array($adm, "tech", $term, $class, $year));
}else{
    $getlang = $common_query_sp;
    $getlang->execute(array($adm, "tech", $term, $class, $year,$exam));
}
while($rst=$getlang->fetch(PDO::FETCH_ASSOC)){
    if($rst['marks']!=null) {
        $technic[$rst['subject']] = $rst['marks'];
        $choices[$rst['subject']] = $rst['marks'];
    }else continue;
}unset($rst);
arsort($technic);

//add optional subjects to choices array
$iteration=0;
foreach($humanities as $sub => $value){
    $iteration++;
    if($iteration >1 && $iteration <= 3){
        $choices[$sub]=$value;
    }elseif($iteration==1){
        $countable[$sub]=$value;
        if($exam=="All") {
            $grades[$sub] = $points[getgradeinSub($adm, $sub, $term, $class, $year)];
        }else{
            $grades[$sub] = $points[getgradeinSpSub($adm, $sub, $term, $class, $year,$exam)];
        }
    }
}unset($humanities);

$iteration=0;
foreach($sciences as $sub => $value){
    $iteration++;
    if($iteration > 2 && $iteration <= 3){
        $choices[$sub]=$value;
    }elseif($iteration<=2){
        $countable[$sub]=$value;
        if($exam=="All") {
            $grades[$sub] = $points[getgradeinSub($adm, $sub, $term, $class, $year)];
        }else{
            $grades[$sub] = $points[getgradeinSpSub($adm, $sub, $term, $class, $year,$exam)];
        }
    }
}unset($sciences);
arsort($choices);

//get required and view the required number
//count number of keys
$count_compasory=array_count_values($monitor);
if($count_compasory['lang']==3 && $count_compasory['sci']>=2 && $count_compasory['hum']>=1){
    $remaining=7-count($countable);
    $iteration=0;
    foreach($choices as $sub => $value){
        $iteration++;
        if($iteration <= $remaining){
            $countable[$sub]=$value;
            if($exam=="All") {
                $grades[$sub] = $points[getgradeinSub($adm, $sub, $term, $class, $year)];
            }else{
                $grades[$sub] = $points[getgradeinSpSub($adm, $sub, $term, $class, $year,$exam)];
            }
        }
    }
    unset($choices);
    unset($countable);
    unset($monitor);
    unset($langs);
    unset($sciences);
    unset($humanities);
    unset($points);

    return array_sum($grades);
}
else unset($choices);
unset($countable);
unset($monitor);
unset($langs);
unset($sciences);
unset($humanities);
unset($grades);
unset($points);

return null;}

A good programmer would consider this some bad practice. Is there a way to write an SQL statement for that? Getting a solution to the problem would be a breakthrough. A picture of the table is attached below.

Answer 1

The best i could come up with in the short time i have, i took the same approach as @GarethParker except for the last mark you need quite a bit more.

You can try it on the sqlfiddle

(SELECT *
FROM averaged_marks
WHERE sub_cat='lang'
ORDER BY marks DESC)             #This select retrieves all 'lang' marks
UNION
(SELECT *
FROM averaged_marks
WHERE sub_cat='sci'
ORDER BY marks DESC
LIMIT 2)             #This select retrieves the highest 2 'sci' marks
UNION
(SELECT *
FROM averaged_marks
WHERE sub_cat='hum'
ORDER BY marks DESC
LIMIT 1)             #This select retrieves the highest 1 'hum' marks
UNION
(SELECT *            #This select retrieves the highest mark of the leftovers
FROM averaged_marks
WHERE averaged_marks.id NOT IN (     #This NOT IN contains the same select as above to get the first 6 marks and to make sure the 7th is not one of them.
    SELECT temp.id
    FROM
        ((SELECT averaged_marks.id
        FROM averaged_marks
        WHERE sub_cat='lang'
        ORDER BY marks DESC)
        UNION
        (SELECT averaged_marks.id
        FROM averaged_marks
        WHERE sub_cat='sci'
        ORDER BY marks DESC
        LIMIT 2)
        UNION
        (SELECT averaged_marks.id
        FROM averaged_marks
        WHERE sub_cat='hum'
        ORDER BY marks DESC
        LIMIT 1)
        ) AS temp)
ORDER BY marks DESC
LIMIT 1)

Answer 2

Without having test data to run against, and being unwilling to create my own, I'm hesitant to craft usable SQL, but it sounds like what you want might be the UNION operator. You can join the results of multiple SQL queries together with it. So you can have your different conditions and merely union them. Again, the below SQL is only an example, and it might not work, you may have to tweak it yourself

SELECT * FROM exmarks WHERE sub_cat='lang'
UNION
SELECT * FROM exmarks WHERE sub_cat='sci' ORDER BY mark DESC LIMIT 2
UNION
SELECT * FROM exmarks WHERE sub_cat='hum' ORDER BY mark DESC LIMIT 1
UNION
SELECT * FROM exmarks WHERE sub_cat NOT IN ('lang', 'sci', 'hum') ORDER BY mark DESC LIMIT 1