PHP Practical Tutorials | jQuery and Webpage Design Home

Using PHP SQL SUM() Function

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.