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 id, name, class,( social + science + math) AS total FROM `student3`
idnameclasstotal
2Max RuinThree226
3ArnoldThree170
4Krish StarFour180
5John MikeFour230
6Alex JohnFour225
7My John RobFifth208
8AsruidFive255
9Tes QrySix208
10Big JohnFour150

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.
idnameclassTotalpercentage
2Max RuinThree22679.2982
3ArnoldThree17059.6491
4Krish StarFour18063.1579
5John MikeFour23080.7018
6Alex JohnFour22578.9474
7My John RobFifth20872.9825
8AsruidFive25589.4737
9Tes QrySix20872.9825
10Big JohnFour15052.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
idnameclasssocialsciencemathTotal
2Max RuinThree858556226
3ArnoldThree557540170
4Krish StarFour607050180
5John MikeFour609080230
6Alex JohnFour558090225
7My John RobFifth787060208
8AsruidFive859080255
9Tes QrySix787060208
10Big JohnFour555540150

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'
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


Visitors Rating
Your Rating


Google+
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" ?

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








HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us
©2000-2016 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer