PHP MySQL - MAX & GROUP BY

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

As we know the name MAX refers as Maximum, that returns the maximum value from the values in the column.

 

Syntax:

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

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

 

Example:

Here, we will get the maximum value 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 return the maximum value from quantity,cost columns
$my_query = "SELECT MAX(Quantity),MAX(Cost) FROM Medicine";


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

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

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

$connecting_data->close();
?>

Output:

 

So we can see that maximum value in Quantity column is 50 and in Cost column is 5000.

 

MAX 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 maximum value from each  grouped data, then you can use GROUP BY Clause along with MAX function.

 

Syntax:

SELECT MAX(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 maximum value from a  column (each group) specified inside the MAX() function.

 

Example 1:

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

$connecting_data->close();
?>

Output:

So Four groups are formed from 7 rows.

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

 

Example 2:

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

$connecting_data->close();
?>

Output:

So Four groups are formed from 7 rows.

For each group, maximum 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 MAX() function and along with GROUP BY(). We discussed three different examples to get the maximum value from the particular column.