# 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
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.

Read more on CASE WHEN THEN statement here.

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'

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>";
?>``````

## Subscribe to our mailing list

* indicates required
Subscribe to plus2net
Visitors Rating

▼ More on Math functions in SQL
 aqlan 19-07-2010 How to calculate Total Mark diffrent between Max Ruin and Arnold?
 stuka 27-02-2012 how to multiply two columns and set the sum as the value of another column in sql server table?
 Javier Rivera 11-10-2012 Nice, but is there a way to add those values to thw query SELECT name, ( social + science + math) AS total FROM `student3` WHERE total>200
 krishnamoorthy M 26-06-2013 math function in oops php using program
 lenny_the_bruce 08-10-2013 How do you take that total and insert the total into another column named total for each row?
 begosew 26-02-2014 How to calculate Percentage different between date in two table. stuka
 lisa 12-03-2014 how to create formula to calculate data integer from different table in sql database server?
 raj 21-05-2014 How to get two max value from single table
 Sundar Shanmuganathan 26-06-2014 I need to dial play all values from table and also calculate all column value and displacing in separate row Name Eng Math Fore Eg : A 10 20 b 20 30 Count(2) 30 50
 Tanmay 09-07-2014 Say we have a column "Total" in student3 table. How can I insert the sum in the "Total" column? Like we do in excel? Can you please help?
 Danni 16-10-2014 How to display total price of products but excluding two different products?
 dipak 27-08-2015 how to display grades from marks using php
 Benjamin Quartey 03-08-2016 This is great. But How do we select for an example the best three subject of a student in a row from about five subjects ? Class Position may not always depend on the sum of all subject but could also depend on the best 3 etc. Kindly assist on that also
 subhendu 08-09-2016 You don't have to use PHP code to get the grade, the same Query can be modified with CASE to list our GRADE of the student. This part will be added shortly.
 subhendu 09-09-2016 Grade is calculated by using CASE , this part is added to the tutorial
 Sandi 20-10-2016 is posible something like... sum 7 columns "SELECT SUM(col1) as a, SUM(col2) as b, SUM(col3) as c, SUM(col4) as d, SUM(col5) as e, SUM(col6) as f, SUM(col7) as g FROM mytable')" and get 5 highest values "limit 5" ?
 ephraim 03-08-2017 how can the total scores be ranked and stored in another table
 subhendu 05-08-2017 You can use order by to get ranked list. You can also use store sum in different table
 John muthama 21-09-2017 How do we position students or rather how do we rank them in scenario where they tie. i.e 75,72,72,72, 69,66,56. Of course 75 position 1, how to we treat 72? because they are all position 2
 subhendu 27-09-2017 You can use order by to rank students based on two columns. Here you can use rank and name so three students having same mark will be listed based on their name. You can read more on order by here.

Post Comment This is for short comments only. Use the forum for more discussions.

## Subscribe to our mailing list

* indicates required
Subscribe to plus2net