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.
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`
id
name
class
total
2
Max Ruin
Three
226
3
Arnold
Three
170
4
Krish Star
Four
180
5
John Mike
Four
230
6
Alex John
Four
225
7
My John Rob
Fifth
208
8
Asruid
Five
255
9
Tes Qry
Six
208
10
Big John
Four
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 id, name, class,( social + science + math) as Total,(( social + science + math)/(95*3) * 100) AS percentage FROM `student3`
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
id
name
class
social
science
math
Total
2
Max Ruin
Three
85
85
56
226
3
Arnold
Three
55
75
40
170
4
Krish Star
Four
60
70
50
180
5
John Mike
Four
60
90
80
230
6
Alex John
Four
55
80
90
225
7
My John Rob
Fifth
78
70
60
208
8
Asruid
Five
85
90
80
255
9
Tes Qry
Six
78
70
60
208
10
Big John
Four
55
55
40
150
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.
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.
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" ?
ephraim
03-08-2017
how can the total scores be ranked and stored in another table
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
subhendu
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.