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 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
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 name, ( social + science + math) as Total,(( social + science + math)/(95*3) * 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
Number of User Comments : 11
| 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? |
|
|
|
|
SQL 