Use MySQL Aggregate Sum Function in PHP

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: