PHP MySQL - MIN & GROUP BY

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

As we know the name MIN refers as Minimum , that returns the minimum value from the values in the column.

 

Syntax:

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

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

 

Example:

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


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

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

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

$connecting_data->close();
?>

Output:

 

So we can see that minimum value in Quantity column is 1 and in Cost column is 25.

 

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

 

Syntax:

SELECT MIN(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 minimum value from a  column (ech group) specified inside the MIN() function.

 

Example 1:

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

$connecting_data->close();
?>

Output:

So Four groups are formed from 7 rows.

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

 

Example 2:

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

$connecting_data->close();
?>

Output:

So Four groups are formed from 7 rows.

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