This is quite common to get the total sum of all records of a specified field of a MySQL database table. PHP has a SQL SUM() function that allow us to get the total sum of a field easily.
Assume we have the following table (earnings):
Username | Income |
alex | 20 |
alex | 30 |
mary | 15 |
mary | 35 |
alex | 40 |
Get Total SUM Income of All Records of a Field
Assume we want to get the total sum of the income field. Then we can use the following SQL statement:
<?
// create SQL
$sql = "SELECT SUM(income) FROM earnings '";
// execute SQL query and get result
$sql_result = mysql_query($sql, $connection) or die ("Couldn't execute query.");
while ($row = mysql_fetch_array($sql_result)) {
$total = $row['SUM(income)'];
}
echo "The total income of all staff are: " . $total;
?>
The following result will display on the screen:
The total income of all staff are: 140
Get Total SUM Income of a User
How about we want to get the sum of a user (alex). Then we can use the following SQL statement:
<?
// create SQL
$sql = "SELECT username, SUM(income) FROM earnings where username = 'alex' '";
// execute SQL query and get result
$sql_result = mysql_query($sql, $connection) or die ("Couldn't execute query.");
while ($row = mysql_fetch_array($sql_result)) {
$total = $row['SUM(income)'];
}
echo "The total income of alex is: " . $total;
?>
The following result will display on the screen:
The total income of alex is: 90
Get Total SUM Income of All Users
This is very useful to get a summary of the total income of all users. The following SQL statement can achieve the results:
<?
// create SQL
$sql = "SELECT username, SUM(income) FROM earnings GROUP BY username '";
// execute SQL query and get result
$sql_result = mysql_query($sql, $connection) or die ("Couldn't execute query.");
while ($row = mysql_fetch_array($sql_result)) {
echo "The total income of ". $row['username']. " is: ". $row['SUM(earnings)'];
echo "<br />";
}
?>
The following result will display on the screen:
The total income of alex is: 90
The total income of mary is: 50
This is the end of simple PHP SQL SUM function tutorial.
Leave a Reply