How to calculate sum of values in different columns in MySQL, mysql, php, calculate sum, calculate sum in sql

How to calculate sum of values in different columns in MySQL

As developers, we are mostly used to calculate the sum of values in the same column which is the most common practice but sometimes you might find you have values in different columns that you are required to get their total. In this article, we will show you how you can use PHP and MySQL to calculate the sum of values that are in different columns.

In the most obvious scenario, we are used to using the SUM keyword while reading data from MySQL database table, for example, if we have a table by the name sales and a column called amount that holds all the values of sales made between particular periods of time or amount collected by selling a single product we do calculate the total as shown below

SELECT SUM(amount) AS totalamount FROM sales;

  • You should note that if you fail to include the AS (known as Alias), the column name will change from initial name amount to SUM(amount) that is why it is necessary to include AS so that you can rename the column to have an identical name as you prefer.
  • For a case like the one above, it will calculate the total of all values in the defined column, to ensure you get exact results, limit your result to a specific value by including the WHERE keyword for example, in the above we could specify the results to only calculate the sum of today’s value by adding WHERE clause as shown below

 

SELECT SUM(amount) AS totalamount FROM sales WHERE dayofsale='22-09-2021';

 

Having laid the foundation for the SUM keyword, let us now focus on calculating the sum of values in different columns of the same table.

Suppose you have a table called grades that stores marks of students for various subjects like the one shown below

student_idsubject1subject2subject3subject4
120302432
240162213
334232016

To get the total score of each student in Mysql you need to use this phrase that is displayed below

  • To calculate the sum of student 1

SELECT (subject1+ subject2+ subject3+ subject4) AS totalscore FROM grades WHERE student_id ='1';

  • To calculate sum of student 2

SELECT (subject1+ subject2+ subject3+ subject4) AS totalscore FROM grades WHERE student_id ='2';

  • To calculate sum of student 3

SELECT (subject1+ subject2+ subject3+ subject4) AS totalscore FROM grades WHERE student_id ='3';

 

The results will be given as a single column called totalscore as that is the Alias name we have assigned to the total

Also, please note the use of (), they are the one that tells the query where to start and stop the SUM phrase and also which columns will be renamed in the Alias phrase.

You will note in Mysql, you will need to calculate the sum of each row independently which will involve a lot of work in the number of students in big. This can be made simple in PHP while using the while loop which could make the queries simpler.

  • Suppose you have students data in a table called students, you can get their details and predefine the student id as below

<?php
$liststudents=mysqli_query($con,"SELECT * FROM students");
While($ls=mysqli_fetch_assoc($liststudents)){
$studentid=$ls["id"];
}
?>

For now, you have the ids of all students already defined in the while loop

  • Next, we can now add the SUM query inside the while loop

 

<?php
//to display students details
$liststudents=mysqli_query($con,"SELECT * FROM students");
While($ls=mysqli_fetch_assoc($liststudents)){
$studentid=$ls["id"];
//to get student marks
$getstudentmarks=mysqli_query($con,"SELECT (subject1+ subject2+ subject3+ subject4) AS totalscore FROM grades WHERE student_id ='$studentid';");
}
?>

Please note in the above scenario, we have replaced the student id value with the variable $studentid we received from the students' table

 

As we have discussed, you can now note the difference between calculating the SUM of values in the same column and values in different columns, and the major one that you should note is that in the same column the keyword SUM is used but in different columns, we use the ‘+’ sign.