Sum sql for data in multiple columns and across rows with Total & Percentage

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.

id	name	class	social	science	math
1	John Deo	Four	75	84	78
2	Max Ruin	Three	54	67	85
3	Arnold	Three	78	65	90
4	Krish Star	Four	50	51	53
5	John Mike	Four	80	78	89
6	Alex John	Four	78	60	60
7	My John Rob	Five	77	68	83
8	Asruid	Five	55	50	55
9	Tes Qry	Six	68	60	62
10	Big John	Four	65	66	76

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

id	name	class	total
1	John Deo	Four	237
2	Max Ruin	Three	206
3	Arnold	Three	233
4	Krish Star	Four	154
5	John Mike	Four	247
6	Alex John	Four	198
7	My John Rob	Five	228
8	Asruid	Five	160
9	Tes Qry	Six	190
10	Big John	Four	207

Multiple columns SUM

Related Tutorial MySQL SUM MySQL Max MySQL Min MySQL Avg

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.

id	name	class	Total	percentage
1	John Deo	Four	237	83.1579
2	Max Ruin	Three	206	72.2807
3	Arnold	Three	233	81.7544
4	Krish Star	Four	154	54.0351
5	John Mike	Four	247	86.6667
6	Alex John	Four	198	69.4737
7	My John Rob	Five	228	80.0000
8	Asruid	Five	160	56.1404
9	Tes Qry	Six	190	66.6667
10	Big John	Four	207	72.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

id	name	class	social	math	science	Total
1	John Deo	Four	75	78	84	237
2	Max Ruin	Three	54	85	67	206
3	Arnold	Three	78	90	65	233
4	Krish Star	Four	50	53	51	154
5	John Mike	Four	80	89	78	247
6	Alex John	Four	78	60	60	198
7	My John Rob	Five	77	83	68	228
8	Asruid	Five	55	55	50	160
9	Tes Qry	Six	68	62	60	190
10	Big John	Four	65	76	66	207

Listing all columns along with aggregate functions like sum(), max() using over() with partition

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;

id	name	max	min
1	John Deo	84	75
2	Max Ruin	85	54
3	Arnold	90	65
4	Krish Star	53	50
5	John Mike	89	78
6	Alex John	78	60
7	My John Rob	83	68
8	Asruid	55	50
9	Tes Qry	68	60
10	Big John	76	65

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 )

id	name	class	social	math	science	Total
1	John Deo	Four	75	78	84	237
2	Max Ruin	Three	54	85	67	206
3	Arnold	Three	78	90	65	233
4	Krish Star	Four	50	53	51	154
5	John Mike	Four	80	89	78	247
6	Alex John	Four	78	60	60	198
7	My John Rob	Five	77	83	68	228
8	Asruid	Five	55	55	50	160
9	Tes Qry	Six	68	62	60	190
10	Big John	Four	65	76	66	207
		Total	680	731	649	2060

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.

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.

id	name	class	social	math	science	Total	grade
1	John Deo	Four	75	78	84	237	A
2	Max Ruin	Three	54	85	67	206	B
3	Arnold	Three	78	90	65	233	A
4	Krish Star	Four	50	53	51	154	FAIL
5	John Mike	Four	80	89	78	247	A
6	Alex John	Four	78	60	60	198	C
7	My John Rob	Five	77	83	68	228	A
8	Asruid	Five	55	55	50	160	FAIL
9	Tes Qry	Six	68	62	60	190	C
10	Big John	Four	65	76	66	207	B

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

Listing all columns along with aggregate functions like sum(), max() using over() with partition

SQL file for SUM query
SQL Math References Sum query Add Sum to different table

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

Questions

What is the purpose of the "SUM" function in SQL?
How is the "SUM" function used to calculate the total of a column in SQL?
Can the "SUM" function be applied to multiple columns simultaneously in SQL?
What is the syntax for using the "SUM" function in SQL?
Can the "SUM" function be used with different data types in SQL? If yes, provide an example.
Are there any considerations for using the "SUM" function with NULL values in SQL?
Can you combine the "SUM" function with other SQL clauses, such as WHERE or GROUP BY, to perform conditional or grouped sums?
Is it possible to give an alias to the result of the "SUM" function in SQL? If yes, provide an example.
Can the "SUM" function be used in conjunction with other aggregate functions in SQL? If yes, provide an example.
Are there any performance considerations when using the "SUM" function in SQL, especially with large datasets?

Subscribe to our YouTube Channel here

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?

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?

Danni	16-10-2014
How to display total price of products but excluding two different products?

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

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 <?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>"; ?> help me please

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.

SUM of Multiple columns of MySQL table