MySQL: Aggregate Sum Function
Before describing the sum function, I will first explain what an aggregate function is.
Introduction
An aggregate function operates on a series of values and returns a single summary value, because an aggregate function typically operates on the values in columns and aggregate functions are also called column functions; they perform a calculation on the values in a set of selected rows and a QUERY that contains one or more aggregate functions is typically referred to as a summary query.
Aggregate: Sum function
The aggregate sum function is used to total the non-null values in the expression.
Syntax
SUM([ALL | DISTINCT] expression)
Table on which the sum function will be performed:
Example of aggregate sum function in PHP:
<?php
// Make a MySQL Connection
$con=mysql_connect("localhost","root","");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("mysql");
print "<h2>MySQL: Sum Price by Type</h2>";
$query = "SELECT Type, SUM(price) FROM product_sale GROUP BY type";
$result = mysql_query($query);
// Print out result
while($row = mysql_fetch_array($result)){
echo "Total ". $row['Type']. " = ". $row['SUM(price)'];
echo "<br />";
}
$query = "SELECT SUM(price) FROM product_sale ";
print "<h2>MySQL: Total Price</h2>";
$result = mysql_query($query);
// Print out result
while($row = mysql_fetch_array($result)){
echo "Total= ". $row['SUM(price)'];
echo "<br />";
}
?>
Note: In the above example the SUM function calculates the total price of products from the product_sale table and also calculates the total price of products on the basis of the same type of items with the help of a group by clause; using the group by clause creates four groups and each group contains a total price of products (same type).
Output: