PHP MySQL - AVG & GROUP BY

Let’s discuss how to perform AVG 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 AVG function in MySQL.

 AVG refers as Average, that returns the average of values in an numeric column.

 

Syntax:

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

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

 

Example:

Here, we will get the average 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 average quantity and average cost of medicines.
$my_query = "SELECT AVG(Quantity),AVG(Cost) FROM Medicine";


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

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

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

$connecting_data->close();
?>

Output:

 

So we can see that avearge of all values in the Quantity column is 18.8571 and Cost column is 910.7143.

 

AVG 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 average of each grouped data, then you can use GROUP BY Clause along with AVG function.

 

Syntax:

SELECT AVG(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 average of values in a column specified inside the AVG() function.

 

Example 1:

In this example, we will return the Average 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 average quantity in all Tablet Groups.
$my_query = "SELECT Tablet, AVG(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']. ", Average Quantity: " . $record_data['AVG(Quantity)']. "<br>";
  }
} else {
  echo "No Data";
}

$connecting_data->close();
?>

Output:

 

So Four groups are formed from 7 rows.

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

 

Example 2:

In this example, we will return the average 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 average cost in all Tablet Groups.
$my_query = "SELECT Tablet, AVG(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']. ", Average Cost: " . $record_data['AVG(Cost)']. "<br>";
  }
} else {
  echo "No Data";
}

$connecting_data->close();
?>

Output:

So Four groups are formed from 7 rows.

For each group, average 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 AVG() function and along with GROUP BY(). We discussed three different examples to get the average values  in a particular column.