How to achive this in a single SQL statement

Question

please i have problem at hand,

I have the following tables in my database MySQL

students

studentID | fname   | mname | lname  |
1         | Wilson  | Dale  | Micheal|



examtype

examtypeID | name |
1          | ca 1
2          | ca 2
3          | ca 3
4          | exam


first_term_result

student_id | examtype_id | subject_id | mark
1          | 1           | 7          | 12
1          | 2           | 7          | 10
1          | 3           | 7          | 9
1          | 4           | 7          | 45

currently i have this SQL statement

SELECT CONCAT(students.fname,' ',students.mname,' ',students.lname) 
    AS student_name, students.studentID, exam_type.name, first_term_result.mark 
    FROM students, exam_type, first_term_result 
    WHERE students.studentID=first_term_result.student_id 
    AND exam_type.exam_typeID=first_term_result.exam_type_id 
    AND first_term_result.subject_id=7

it works as i get a table where i can use to get an array like this

How i got it to that array using codeigniter framework :

    $sheet = $this->teacher_model->spreadsheet(); 
    $data = array();
    foreach($sheet as $row)
    {
        $data[$row['student_name']]['studentID']=$row['studentID'];
        $data[$row['student_name']][$row['name']]=$row['mark'];

    }


Array
(
  [Wilson Dale Micheal] => Array
    (
        [studentID] => 1
        [CA 1] => 12
        [CA 2] => 10
        [CA 3] => 9
        [Exam] => 45
    )

)

another SQL statement as follows:

SELECT CONCAT(students.fname,' ',students.mname,' ',students.lname) 
    AS students_name, students.studentID, SUM(first_term_result.mark) as Total
    FROM students, first_term_result 
    WHERE students.studentID=first_term_result.student_id 
    AND first_term_result.subject_id=7 group by students_name

gives something like this:

Array
(
    [Wilson Dale Micheal] => Array
    (
        [studentID] => 1
        [Total] => 76
    )

)

Merging the two array i get my result, which is:

this is how i merged merged the two, the function array_merge() didnt work so i used:

 $data = array();
    foreach($sheet as $row)
    {
        $data[$row['student_name']]['studentID']=$row['studentID'];
        $data[$row['student_name']][$row['name']]=$row['mark'];

    }

    foreach($total as $tol)
    {
        $data[$tol['student_name']]['Total']=$tol["Total"];
    }

Array
(
    [Wilson Dale Micheal] => Array
    (
        [studentID] => 1
        [CA 1] => 12
        [CA 2] => 10
        [CA 3] => 9
        [Exam] => 45
        [Total] => 76
    )

)

But i am wondering if i can achieve all these with a single SQL statement so that i will be able to make ORDER BY Total DESC, so the student with the highest score will come first and so on. The current statement doesnt sort the Total as DESC though i have the array just like i want to.

Thanks

Whats making the results in to an array ? put It in it there — exussum, Oct 08 '13 at 16:08
I believe you were looking for `GROUP BY WITH ROLLUP`. See http://dev.mysql.com/doc/refman/5.7/en/group-by-modifiers.html — PM 77-1, Oct 08 '13 at 16:10
http://stackoverflow.com/questions/12475850/how-can-an-sql-query-return-data-from-multiple-tables — iguider, Oct 08 '13 at 16:11
if examtype is dynamic you want to create an pivot function (because you want rows data to columns data) — Raymond Nijland, Oct 08 '13 at 18:48
@PM77-1 thanks i think that is what i need, but i dont know how to do it, i want a total of (ca1, ca2, ca3, exam) for each students — user2666633, Oct 09 '13 at 15:12

score 1 · Accepted Answer · answered Oct 09 '13 at 16:43

SELECT CONCAT(s.fname,' ',s.mname,' ',s.lname) AS student_name,        
       MAX(s.studentID) As StudentID,
       e.name As Name, 
       SUM(f.mark) As Mark
FROM Students s 
JOIN First_Term_Result f ON s.studentID = f.student_id 
JOIN Exam_Type e ON e.examtypeID = f.examtype_id
WHERE f.subject_id=7
GROUP BY CONCAT(s.fname,' ',s.mname,' ',s.lname), e.name WITH ROLLUP;

The result based on your sample data will be:

STUDENT_NAME    STUDENTID   NAME    MARK
Wilson Dale Micheal    1    ca 1    12
Wilson Dale Micheal    1    ca 2    10
Wilson Dale Micheal    1    ca 3    9
Wilson Dale Micheal    1    exam    45
Wilson Dale Micheal    1    (null)  76
(null)                 1    (null)  76

ROLLUP added two rows:

total for a student (will appear for each student)
grand total (once for all students)

If your version of MySQL supports GROUPING function, you can have your own labels instead of NULL in "total" rows.

Here's SQL Fiddle where you can test by modifying tables content.

Nice +1 this is more or less the pivot I mean .. @user2666633 if you use this make sure you index like this see demo http://sqlfiddle.com/#!2/b1a75/1 — Raymond Nijland, Oct 09 '13 at 17:26

How to achive this in a single SQL statement

1 Answers1