I have a DB that stores student records and there are 4 tables that I need to query from to get a student grade report.
The tables being JOIN are school, students, subject, student_score, class
The table structure are as follows:
Table schools
id | name | city_code
Table students
id | firstname | surname | school_id
Table subjects
id | name | subject_code
Table class
id | name
Table student_score
id | student_id | subject_id | school_id | class_id | academic_year | academic_term
The expected result is meant to fetches all the schools in a specific city (using city_code) and also all the students in each school and all the students with their respective score for a specific academic year & term. Lastly, each student offers multiple subjects and as such the query fetches all the various score for each subject that was offered by each student.
Currently, this query below fetches all query result based on the desired conditions BUT the student scores are in multiple rows.
SELECT A.name, CONCAT_WS(' ', B.firstname, B.surname) AS fullname, B.regnum AS CAS, D.subject_name,
C.ca_score, C.exam_score FROM schools A
INNER JOIN students B ON A.id = B.school_id
LEFT JOIN student_score C ON C.student_id = B.id
LEFT JOIN subjects D ON D.id = C.subject_id
WHERE A.city_code = 5 AND
C.academic_term = 'First' AND C.academic_year = '2023' AND C.class_id = 9;
The above query isn't giving the desired output or result. Each row is meant or expected to be strictly for a specific student and his/her various subject and score are displayed or grouped on that single row based on the student_id.
So, the result I need to fetch from these tables needs to displayed as shown below:
Desired Result structure
school_name | student_name | subject_code_one_ca_score | subject_code_one_exam_score | subject_code_two_ca_score | subject_code_two_exam_score | subject_code_three_ca_score | subject_code_three_exam_score | subject_code_four_ca_score | subject_code_four_exam_score
Below is a sample of the desired result with sample data
Desired Result sample
school_name | student_name | english_ca_score | english_exam_score | maths_ca_score | maths_exam_score | physics_ca_score | physics_exam_score | chemistry_ca_score | chemistry_exam_score
St. Pet Sch | John V. Doe | 20 | 51 | 22 | 49 | 24 | 55 | 22 | 48
Merlin Sch | Jane B. Doe | 19 | 53 | 21 | 50 | 21 | 48 | 24 | 51
Belfast Sch | James P. Doe | 24 | 50 | 18 | 52 | 22 | 50 | 19 | 47
I have attempted using GROUP BY clause to merge the student related data on same row but I get the group clause error
GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Also, I can't seem to figure how to concat or prepend each subject_code value with the original score column names (ca_score and exam_score)
You should avoid using arbitrary letters for table aliases and use abbreviations instead. Something like:
SELECT sch.name, CONCAT_WS(' ', st.firstname, st.surname) AS fullname, st.regnum AS CAS,
SUM(IF(sub.subject_code = 'ENGLISH', ss.ca_score, NULL)) AS english_ca_score,
SUM(IF(sub.subject_code = 'ENGLISH', ss.exam_score, NULL)) AS english_exam_score,
SUM(IF(sub.subject_code = 'MATHS', ss.ca_score, NULL)) AS maths_ca_score,
SUM(IF(sub.subject_code = 'MATHS', ss.exam_score, NULL)) AS maths_exam_score,
...
FROM schools sch
JOIN students st ON sch.id = st.school_id
JOIN student_score ss ON ss.student_id = st.id
JOIN subjects sub ON sub.id = ss.subject_id
WHERE sch.city_code = 5
AND ss.academic_term = 'First'
AND ss.academic_year = '2023'
AND ss.class_id = 9
GROUP BY sch.id, st.id;