Sum sql for data in multiple columns and across rows for Total & Percentage

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.

id	name	class	social	science	math
2	Max Ruin	Three	85	56	85
3	Arnold	Three	55	40	75
4	Krish Star	Four	60	50	70
5	John Mike	Four	60	80	90
6	Alex John	Four	55	90	80
7	My John Rob	Fifth	78	60	70
8	Asruid	Five	85	80	90
9	Tes Qry	Six	78	60	70
10	Big John	Four	55	40	55

Related Tutorial
• MySQL SUM • MySQL Max • MySQL Min • MySQL Avg

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 name, ( social + science + math) AS total FROM `student3`

name	total
Max Ruin	226
Arnold	170
Krish Star	180
John Mike	230
Alex John	225
My John Rob	208
Asruid	255
Tes Qry	208
Big John	150

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

total
1852

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)/(953) 100) AS percentage FROM `student3`

Displaying Total Mark with Percentage

Both total mark and percentage we can display like this

SELECT name, ( social + science + math) as Total,(( social + science + math)/(953) 100) AS percentage FROM `student3`

The Output is here.

name	Total	percentage
Max Ruin	226	79.2982
Arnold	170	59.6491
Krish Star	180	63.1579
John Mike	230	80.7018
Alex John	225	78.9474
My John Rob	208	72.9825
Asruid	255	89.4737
Tes Qry	208	72.9825
Big John	150	52.6316

The above percentage data is having four decimal places, we can format these numbers by using ROUND Sql command.

Sum of the row data of each record

Now we will try to display all marks and sum of them for each student. Here is the query

SELECT id,name, social, math, science, sum(social + math + science ) as total from student3 group by id

The output is here

id	name	social	math	science	total
2	Max Ruin	85	85	56	226
3	Arnold	55	75	40	170
4	Krish Star	60	70	50	180
5	John Mike	60	90	80	230
6	Alex John	55	80	90	225
7	My John Rob	78	70	60	208
8	Asruid	85	90	80	255
9	Tes Qry	78	70	60	208
10	Big John	55	55	40	150

Storing total mark in different table.

We can store sum of marks in each subjects of each student in a different table. This will be useful for final report printing or displaying.

Storing sum of each record in different table

PHP code to display

We can execute the above code and display records by using PHP. Within the code first we will connect to MySQL database.

<?Php
require "config.php"; // Database connection

$count="SELECT id,name, social, math, science, sum(social + math + science ) as total from student3 group by id";

echo "<table>";
echo "<tr><th>id</th><th>name</th><th>social</th><th>math</th><th>science</th><th>total</th></tr>";
foreach ($dbo->query($count) as $row) {
echo "<tr ><td>$row[id]</td><td>$row[name]</td><td>$row[social]</td><td>$row[math]</td><td>$row[science]</td><td>$row[total]</td></tr>";
}
echo "</table>";
?>

Download the SQL dump of the above student table

SUM of Multiple columns of MySQL table