PHP MySQL - SUM & GROUP BY

Let’s discuss how to perform SUM aggregate function and how to use this aggregate function with GROUP BY function through PHP to connect with the MySQL database in the XAMPP Server.

 

Consider the table - Medicine present in the database - hospital snapshot with some records.

Now, we will discuss about SUM function in MySQL.

As we know the name SUM refers as Total, that returns the total sum of values in an numeric column.

 

Syntax:

SELECT SUM(column1),SUM(column2),......... FROM TABLE_NAME

So, column1, column2 represents the numeric columns in which the total sum is returned.

 

Example:

Here, we will get the total sum of values from Quantity and Cost columns.

<?php

// Specify the server
$server = "localhost";

// Specify the user
$user = "root";

// Specify the password
$pwd = "";

// Specify the database 
$data = "hospital";

// Let's create connection by using the above details
$connecting_data =  new mysqli($server, $user, $pwd, $data);

// Mention the SQL Query to get total quantity and total cost of medicines.
$my_query = "SELECT SUM(Quantity),SUM(Cost) FROM Medicine";


$return = $connecting_data->query($my_query);

if ($return->num_rows > 0) {
 
  while($record_data = $return->fetch_assoc()) {

    echo "Total Quantity: " . $record_data['SUM(Quantity)']. " , Total Cost: " . $record_data['SUM(Cost)']. "<br>";
  }
} else {
  echo "No Data";
}

$connecting_data->close();
?>

Output:

 

So we can see that sum of all values in the Quantity column is 132 and Cost column is 6375.

 

SUM WITH GROUP BY()

 

GROUP BY() is used to group the similar values in a row.

If you want to GROUP the columns and return the sum of each grouped data, then you can use GROUP BY Clause along with SUM function.

 

Syntax:

SELECT SUM(column),other columns......... FROM TABLE_NAME GROUP BY(column)

In the column specified inside GROUP BY, all the similar values are grouped and return the sum of values in a column specified inside the SUM() function.

 

Example 1:

In this example, we will return the total Quantity from all Tablet Groups.

<?php

// Specify the server
$server = "localhost";

// Specify the user
$user = "root";

// Specify the password
$pwd = "";

// Specify the database 
$data = "hospital";

// Let's create connection by using the above details
$connecting_data =  new mysqli($server, $user, $pwd, $data);

// Mention the SQL Query to get total quantity in all Tablet Groups.
$my_query = "SELECT Tablet, SUM(Quantity) FROM Medicine GROUP BY Tablet";


$return = $connecting_data->query($my_query);

if ($return->num_rows > 0) {
 
  while($record_data = $return->fetch_assoc()) {

    echo "Tablet: " . $record_data['Tablet']. ", Total Quantity: " . $record_data['SUM(Quantity)']. "<br>";
  }
} else {
  echo "No Data";
}

$connecting_data->close();
?>

Output:

So Four groups are formed from 7 rows.

For each group, total quantity is returned (Here we are selecting Tablet and Quantity columns).

 

Example 2:

In this example, we will return the total Cost from all Tablet Groups.

<?php

// Specify the server
$server = "localhost";

// Specify the user
$user = "root";

// Specify the password
$pwd = "";

// Specify the database 
$data = "hospital";

// Let's create connection by using the above details
$connecting_data =  new mysqli($server, $user, $pwd, $data);

// Mention the SQL Query to get total cost in all Tablet Groups.
$my_query = "SELECT Tablet, SUM(Cost) FROM Medicine GROUP BY Tablet";


$return = $connecting_data->query($my_query);

if ($return->num_rows > 0) {
 
  while($record_data = $return->fetch_assoc()) {

    echo "Tablet: " . $record_data['Tablet']. ", Total Cost: " . $record_data['SUM(Cost)']. "<br>";
  }
} else {
  echo "No Data";
}

$connecting_data->close();
?>

Output:

So Four groups are formed from 7 rows.

For each group, total cost is returned (Here we are selecting Tablet and Cost columns).

 

Conclusion

So by the end of this article, we saw how to perform PHP-MySQL query with SUM() function and along with GROUP BY(). We discussed three different examples to get the total sum of values in a particular column.