SUM of Multiple columns of MySQL table


Sum of multiple columns 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
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;
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>"; ?>
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



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





    SQL Video Tutorials










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