PHP MySQL tutorial

GROUP BY and HAVING Clauses in MySQL

The GROUP BY clause is used to group the output of the aggregate functions with respect to some specific column. Consider a scenario where you want to group all the patients with same age into one group and then get the count of all the patient groups with respect to age. In such scenarios you can use GROUP BY statements. The syntax of GROUP BY statement is as follows.

SELECT column, AggFunc(column)
FROM table
GROUP BY column;

In the following example, we shall first group all the patients with same age into one group. This will give us multiple groups with respect to age. After that we shall apply Count aggregate function on each group to get the total number of patient in each group. The number of rows returned will be equal to number of groups. Each row will have two columns: One for the age and the other for the number of patients having that age. Have a look at the following code.




Using GROUP BY Function in Mysqli



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 
    
$query = "Use Hospital;";
$connection->query($query);
    
// Using GROUP BY Function

$query = "SELECT patient_age, COUNT(patient_fname) as 'tpatients' ".
"FROM Patient ".
"GROUP BY patient_age";

$output = $connection->query($query);

if ($output->num_rows > 0) {
    // output data of each row
    while($row = $output->fetch_assoc()) {
        echo "Age " . $row["patient_age"]." Total Patients " . $row["tpatients"]."
"; } } else { echo "0 results"; } $connection->close(); ?>

Download the code

HAVING Clause

With simple MySQL queries, WHERE statement is used. However, with GROUP BY clause, HAVING statement is used to filter data. For instance, if we want to group all the patients with age greater than 35, we can extend the previous example to include HAVING statement as follows.




Using GROUP BY with HAVING Clause in Mysqli



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 
    
$query = "Use Hospital;";
$connection->query($query);
    
// Using GROUP BY with HAVING Clause

$query = "SELECT patient_age, COUNT(patient_fname) as 'tpatients' ".
"FROM Patient ".
"GROUP BY patient_age ".
"HAVING patient_age > 35";

$output = $connection->query($query);

if ($output->num_rows > 0) {
    // output data of each row
    while($row = $output->fetch_assoc()) {
        echo "Age " . $row["patient_age"]." Total Patients " . $row["tpatients"]."
"; } } else { echo "0 results"; } $connection->close(); ?>

Download the code

<<< UCASE and LCASE Functions in MySQLINSERT INTO SELECT in MySQL >>>
Copyright 2005-2016 KnowledgeHills. Privacy Policy. Contact .