SQL PHP HTML ASP JavaScript articles and free scripts to download
SQL Math Functions

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.
idnameclasssocialsciencemath
2Max RuinThree855685
3ArnoldThree554075
4Krish StarFour605070
5John MikeFour608090
6Alex JohnFour559080
7My John RobFifth786070
8AsruidFive858090
9Tes QrySix786070
10Big JohnFour554055
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`

nametotal
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)/(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.
nameTotalpercentage
Max Ruin22679.2982
Arnold17059.6491
Krish Star18063.1579
John Mike230 80.7018
Alex John225 78.9474
My John Rob 208 72.9825
Asruid255 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
idnamesocialmathsciencetotal
2 Max Ruin 85 85 56 226
3 Arnold5575 40 170
4 Krish Star60 70 50 180
5 John Mike609080230
6 Alex John558090225
7 My John Rob787060208
8Asruid85 9080255
9Tes Qry787060208
10Big John 55 5540150

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 : 10


Google+

aqlan19-07-2010
How to calculate Total Mark diffrent between Max Ruin and Arnold?
stuka27-02-2012
how to multiply two columns and set the sum as the value of another column in sql server table?
Javier Rivera11-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 M26-06-2013
math function in oops php using program
lenny_the_bruce08-10-2013
How do you take that total and insert the total into another column named total for each row?

begosew26-02-2014
How to calculate Percentage different between date in two table.

stuka
lisa12-03-2014
how to create formula to calculate data integer from different table in sql database server?
raj21-05-2014
How to get two max value from single table
Sundar Shanmuganathan26-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

Tanmay09-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?
Post Comment This is for short comments only. Use the forum for more discussions.
Name
Email( not to be displayed)Privacy Policy
1+2=This is to prevent automatic submission by spammers. Please enter the result of the sum as asked




HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us

©2000-2014 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer