# SUM of Multiple columns of MySQL table

``SELECT sum( column_name ) FROM table_name``
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
1John DeoFour758478
2Max RuinThree546785
3ArnoldThree786590
4Krish StarFour505153
5John MikeFour807889
6Alex JohnFour786060
7My John RobFive776883
8AsruidFive555055
9Tes QrySix686062
10Big JohnFour656676

Sum of values of columns in MySQL table with GROUP BY , IN and CASE

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 student_sum``````
idnameclasstotal
1John DeoFour237
2Max RuinThree206
3ArnoldThree233
4Krish StarFour154
5John MikeFour247
6Alex JohnFour198
7My John RobFive228
8AsruidFive160
9Tes QrySix190
10Big JohnFour207

## 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 student_sum``````
 total 2060
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 `student_sum```````

## 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 `student_sum```````
The Output is here.
idnameclassTotalpercentage
1John DeoFour23783.1579
2Max RuinThree20672.2807
3ArnoldThree23381.7544
4Krish StarFour15454.0351
5John MikeFour24786.6667
6Alex JohnFour19869.4737
7My John RobFive22880.0000
8AsruidFive16056.1404
9Tes QrySix19066.6667
10Big JohnFour20772.6316
The above percentage data is having four decimal places, we can format these numbers by using ROUND Sql command. Query to display percentage value using 2 decimal places.
``````SELECT id, name, class,( social + science + math) as Total,
FORMAT((( social + science + math)/(95*3) * 100),2) AS percentage
FROM `student_sum```````

## Sum of the row data of each record

Now we will try to display all marks and sum of them for each student by using group by. Note that id is unique value column in our table. Here is the query
``````SELECT id,name,class, social, math, science,
sum(social + math + science ) as Total
from student_sum GROUP BY id``````
The output is here
idnameclasssocialmathscienceTotal
1John DeoFour757884237
2Max RuinThree548567206
3ArnoldThree789065233
4Krish StarFour505351154
5John MikeFour808978247
6Alex JohnFour786060198
7My John RobFive778368228
8AsruidFive555550160
9Tes QrySix686260190
10Big JohnFour657666207

## Displaying from Highest to lowest

We can list starting from highest mark to lowest mark by using Order By Query.
``````SELECT id,name,class, social, math, science,
sum(social + math + science ) as Total
from student_sum GROUP BY id ORDER BY Total DESC``````

## Displaying highest & lowest mark in all subjects of each student

``````SELECT id,name,GREATEST(social,science,math) as max,
LEAST(social,science,math) as min FROM student_sum;``````
idnamemaxmin
1John Deo8475
2Max Ruin8554
3Arnold9065
4Krish Star5350
5John Mike8978
6Alex John7860
7My John Rob8368
8Asruid5550
9Tes Qry6860
10Big John7665

## Displaying sum of all columns at last row

Like we do in MS Excel we can display sum of all columns and the sum of all total subject marks by using UNION. Note that we have to maintain same number of columns while using UNION query.
``````SELECT id,name,class, social, math, science,
sum(social + math + science ) as Total
from student_sum GROUP BY id
UNION
SELECT '','','Total',sum(social),sum(math),sum(science),
sum(social)+sum(math)+sum(science)  from student_sum ``````
Output ( watch the last row )
idnameclasssocialmathscienceTotal
1John DeoFour757884237
2Max RuinThree548567206
3ArnoldThree789065233
4Krish StarFour505351154
5John MikeFour808978247
6Alex JohnFour786060198
7My John RobFive778368228
8AsruidFive555550160
9Tes QrySix686260190
10Big JohnFour657666207
Total6807316492060

## 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 student_sum GROUP BY id``````
Output is here.
idnameclasssocialmathscienceTotalgrade
1John DeoFour757884237A
2Max RuinThree548567206B
3ArnoldThree789065233A
4Krish StarFour505351154FAIL
5John MikeFour808978247A
6Alex JohnFour786060198C
7My John RobFive778368228A
8AsruidFive555550160FAIL
9Tes QrySix686260190C
10Big JohnFour657666207B

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

SQL file for SUM query

Answers of questions posted by visitors on SQL sum command MySQL query

## Questions

Subscribe to our YouTube Channel here

## Subscribe

* indicates required
Subscribe to plus2net

plus2net.com
 aqlan 19-07-2010 How to calculate Total Mark diffrent between Max Ruin and Arnold?
 chethan 03-03-2011 How to find top scorer in this table?..i mean it should display o/p as Asruid 255 89.4737 . Please reply with ans as early as possible.
 linn 04-08-2011 how to add values in 2 different fields of the same table?
 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
 bhushan 27-06-2014 not getting o/p i have table with name and subx and suby i want to show name in depend upon total marks...
 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?
 rexon 30-07-2014 2. 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?
 Mukesh 18-09-2014 How can we add three columns of three tables based on their id means that from three tables add the amount where id=1,1,1, same add amount of id=2,2,2
 Danni 16-10-2014 How to display total price of products but excluding two different products?
 wilfred 24-05-2015 hi getting stack i tried using this code i got from your blog but its showing fatal error on the foreach line "; echo "
idnamesocialmathsciencetotal
\$row[id] \$row[name]\$row[social]\$row[math]\$row [science]\$row[total]
"; foreach (\$dbo->query(\$count) as \$row) { echo ""; } echo ""; ?> help me please
 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
 smo1234 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.
 smo1234 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
 smo1234 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
 smo1234 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.
 Usha Kalamani 29-09-2018 Hello, I have tried the above method and that's working fine. But, I want to insert result from Excel Sheet into database which is stored in different format. For every student I want to add marks in a same column titled marks. How to achieve that.
 Nikkolai 10-02-2019 Is it good practice to use such sql query like this in a php or use php to do the calculation? thanks.
 smo1234 11-02-2019 Query is better.
 smo1234 13-02-2019 There is a plugin available to port excel data to mysql. You can also use csv format to read excel data into mysql.
 30-08-2019 you need to end queries with semicolon (;)
 21-03-2020 how to add one row after fetching all rows and column with php & mySQL?
 24-05-2020 How can we calculate sum of multiple dynamic columns? For example my columns are between s1 and s13. But These columns can be changable. I am trying the codes below but I couldn't get any result. For x =1 to 13 select sum (s"&x&") AS total from students next
 07-01-2021 Can we calculate sum of three columns while creating a table Ex=gross_sal(hra+da+basic_sal) And when we enter value gross_sal gives the sum of these 3 within the bracket

Post your comments , suggestion , error , requirements etc here

We use cookies to improve your browsing experience. . Learn more
 HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer