We have seen how the sum function is used to get the total value of a column in a mysql table. Now we will learn how to get the query for sum in multiple columns and for each record of a table. For a better understanding we will change our student table a bit by adding marks in different subjects for each record. Now the table will be like this.
We will write a query to display sum of all marks of each student. Note that here we will not use sum sql function inside our query. Here is the code.
SELECT id, name, class,( social + science + math) AS total FROM `student3`
My John Rob
Multiple columns SUM
As you can see the above sum is sum of marks of each student. This is horizontal sum in our table. Now we will try to get sum of marks of all students in all subjects. So the sum is to be collected in all three columns.
SELECT sum(social + math + science ) as total from student3
We can add various other sql commands like where clause etc.
Calculating Percentage of Marks
Let us assume the full mark in each subject is 95. So to get the percentage mark of each student we have to multiply 95 with 3 ( for three subjects ) and then divide that from the total and multiply with 100 here is the query.
SELECT name, (( social + science + math)/(95*3) * 100) AS percentage FROM `student3`
Displaying Total Mark with Percentage
Both total mark and percentage we can display like this
SELECT id, name, class,( social + science + math) as Total,(( social + science + math)/(95*3) * 100) AS percentage FROM `student3`