DavidoLLP DavidoLLP - 2 months ago 5
MySQL Question

Suming multiple colums in a single MYSQL table

+---------+------------+------------+-----------------------+-----------------+---------------------+
| exam_id | student_id | subject_id | assignment_mark_total | test_mark_total | exams_mark_obtained |
+---------+------------+------------+-----------------------+-----------------+---------------------+
| 1 | 19 | 1 | 20 | 30 | 50 |
| 1 | 20 | 1 | 18 | 27 | 48 |
| 1 | 21 | 1 | 16 | 27 | 49 |
| 1 | 22 | 1 | 16 | 29 | 47 |
| 1 | 23 | 1 | 0 | 0 | 0 |
| 1 | 24 | 1 | 0 | 0 | 0 |
| 1 | 25 | 1 | 0 | 0 | 0 |
| 1 | 26 | 1 | 0 | 0 | 0 |
| 1 | 27 | 1 | 0 | 0 | 0 |
| 1 | 28 | 1 | 0 | 0 | 0 |
| 1 | 29 | 1 | 0 | 0 | 0 |
| 1 | 30 | 1 | 0 | 0 | 0 |
| 1 | 31 | 1 | 0 | 0 | 0 |
| 1 | 32 | 1 | 0 | 0 | 0 |
| 1 | 33 | 1 | 0 | 0 | 0 |
| 1 | 34 | 1 | 0 | 0 | 0 |
| 1 | 35 | 1 | 0 | 0 | 0 |
| 1 | 36 | 1 | 0 | 0 | 0 |
| 1 | 37 | 1 | 0 | 0 | 0 |
| 1 | 38 | 1 | 0 | 0 | 0 |
| 1 | 39 | 1 | 0 | 0 | 0 |
| 2 | 19 | 1 | 20 | 21 | 49 |
| 2 | 20 | 1 | 20 | 21 | 50 |
| 2 | 21 | 1 | 16 | 27 | 46 |
| 2 | 22 | 1 | 0 | 0 | 0 |
| 2 | 23 | 1 | 0 | 0 | 0 |
| 2 | 24 | 1 | 0 | 0 | 0 |
| 2 | 25 | 1 | 0 | 0 | 0 |
| 2 | 26 | 1 | 0 | 0 | 0 |
| 2 | 27 | 1 | 0 | 0 | 0 |
| 2 | 28 | 1 | 0 | 0 | 0 |
| 2 | 29 | 1 | 0 | 0 | 0 |
| 2 | 30 | 1 | 0 | 0 | 0 |
| 2 | 31 | 1 | 0 | 0 | 0 |
| 2 | 32 | 1 | 0 | 0 | 0 |
| 2 | 33 | 1 | 0 | 0 | 0 |
| 2 | 34 | 1 | 0 | 0 | 0 |
| 2 | 35 | 1 | 0 | 0 | 0 |
| 2 | 36 | 1 | 0 | 0 | 0 |
| 2 | 37 | 1 | 0 | 0 | 0 |
| 2 | 38 | 1 | 0 | 0 | 0 |
| 2 | 39 | 1 | 0 | 0 | 0 |
| 3 | 19 | 1 | 0 | 27 | 36 |
| 3 | 20 | 1 | 0 | 24 | 41 |
| 3 | 21 | 1 | 0 | 25 | 48 |
| 3 | 22 | 1 | 0 | 0 | 0 |
| 3 | 23 | 1 | 0 | 0 | 0 |
| 3 | 24 | 1 | 0 | 0 | 0 |
| 3 | 25 | 1 | 0 | 0 | 0 |
| 3 | 26 | 1 | 0 | 0 | 0 |
| 3 | 27 | 1 | 0 | 0 | 0 |
| 3 | 28 | 1 | 0 | 0 | 0 |
| 3 | 29 | 1 | 0 | 0 | 0 |
| 3 | 30 | 1 | 0 | 0 | 0 |
| 3 | 31 | 1 | 0 | 0 | 0 |
| 3 | 32 | 1 | 0 | 0 | 0 |
| 3 | 33 | 1 | 0 | 0 | 0 |
| 3 | 34 | 1 | 0 | 0 | 0 |
| 3 | 35 | 1 | 0 | 0 | 0 |
| 3 | 36 | 1 | 0 | 0 | 0 |
| 3 | 37 | 1 | 0 | 0 | 0 |
| 3 | 38 | 1 | 0 | 0 | 0 |
| 3 | 39 | 1 | 0 | 0 | 0 |
+---------+------------+------------+-----------------------+-----------------+---------------------+
63 rows in set (0.00 sec)


Hello, i have a sample database for a school report sheet,i want to be able to get an array result with sum total of individual subjects for the 3 terms

i will like to have a result like this, as a sum total of each subject's
(assignment_mark_total + test_mark_total + exams_mark_obtained)
AS scores where
exam_id
=1&2&3

+--------+
| scores |
+--------+
| 253 |
| 249 |
| 254 |
| 92 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
+--------+

Answer

I'm assuming you're looking for total marks, across the 3 exams, for each student. Try this query:

SELECT
    SUM(assignment_mark_total + test_mark_total + exams_mark_obtained) AS scores
FROM
    table_name
WHERE
    exam_id in (1, 2, 3)
GROUP BY 
    student_id

You might also want to add student_id in the selected column list.

Comments