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

SUM of Multiple columns of MySQL table

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

id	name	class	total
2	Max Ruin	Three	226
3	Arnold	Three	170
4	Krish Star	Four	180
5	John Mike	Four	230
6	Alex John	Four	225
7	My John Rob	Fifth	208
8	Asruid	Five	255
9	Tes Qry	Six	208
10	Big John	Four	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)/(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`

The Output is here.

id	name	class	Total	percentage
2	Max Ruin	Three	226	79.2982
3	Arnold	Three	170	59.6491
4	Krish Star	Four	180	63.1579
5	John Mike	Four	230	80.7018
6	Alex John	Four	225	78.9474
7	My John Rob	Fifth	208	72.9825
8	Asruid	Five	255	89.4737
9	Tes Qry	Six	208	72.9825
10	Big John	Four	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,class, social, math, science, sum(social + math + science ) as Total from student3 group by id

The output is here

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

Show Grade based on the total mark ( using CASE statement )

We can display grade of the student by using CASE statement in our Query. MySQL Case query executes the statement based on the matching condition. Here we will check total mark against set marks for different levels of grade and allot Grade accordingly.

Here is our query.

SELECT id,name,class, social, math, science, sum(social + math + science ) as Total ,
CASE WHEN  sum(social + math + science ) >= 225 THEN 'A' 
 WHEN  sum(social + math + science ) >=200  THEN 'B' 
WHEN  sum(social + math + science ) >=170  THEN 'C'
ELSE   'FAIL'
END AS grade 

from student3 group by id

Output is here.

id	name	class	social	math	science	Total	grade
2	Max Ruin	Three	85	85	56	226	A
3	Arnold	Three	55	75	40	170	C
4	Krish Star	Four	60	70	50	180	C
5	John Mike	Four	60	90	80	230	A
6	Alex John	Four	55	80	90	225	A
7	My John Rob	Fifth	78	70	60	208	B
8	Asruid	Five	85	90	80	255	A
9	Tes Qry	Six	78	70	60	208	B
10	Big John	Four	55	55	40	150	FAIL

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

▼ More on Math functions in SQL