How To Use SQL HAVING STATEMENT With Example In PHP

Posted By Bajarangi soft , Posted On 18-09-2020

HAVING Clause perform in SQL as a condition with GROUP BY Clause. HAVING Clause added in the SQL because WHERE Clause cannot be combined with aggregate functions.The WHERE clause is used to filter records,whereas the HAVING clause places conditions on grouped by the GROUP BY clause.

Syntax SQL HAVING Clause

SELECT column_name list
FROM table_name
WHERE condition
GROUP BY column_name list
HAVING condition
ORDER BY column_name list;

Lets Learn.
1.Create database and table.
Student table.

StudentID	StudentName	Address	Country	Section	Branch	Percentage	CourseID
1	Liam	23 Andrew road	USA	A	ISE	66	5
2	Andrew	2415 Anthony market str	Germany	A	ISE	80	6
4	Ryan	18 Christopher sq.	INDIA	B	EE	67	5
5	Charles	878 Eli Berguvsvägen 8	UK	C	ECE	52	8
8	Anikth	23 Andrew road	AFRICA	A	CSE	66	3
9	Andrew	2415 Anthony market str	Germany	A	ISE	80	2
10	Bhavya	18 Christopher sq.	INDIA	B	CE	67	2
11	Charles	878 Eli Berguvsvägen 8	INDIA	CL	CE	52	2
12	Andrew	878 Eli Berguvsvägen 8	INDIA	C	CL	52	3
13	Daniel	878 Eli Berguvsvägen 8	USA	ME	CSE	52	5
14	Jackson	878 Eli Berguvsvägen 8	USA	E	CSE	52	5
15	Kiran	878 Eli Berguvsvägen 8	INDIA	C	ME	52	3
16	Kiran	878 Eli Berguvsvägen 8	INDIA	ME	CE	52	3
17	Kiran	banglore	INDIA	A	ME	66	3

Example(1)

SELECT COUNT(StudentID) AS Numberofstudents, `Country` FROM student GROUP BY `Country` HAVING NumberofRecords > 2

above SQL statement selecting studentid and country from student table to lists the number of student in each country As Numberofstudents and counts the country which more than 2 students by SQL HAVING Clause

Example(2)

SELECT COUNT(StudentID) AS NumofstudentsBranch , `Branch` FROM student GROUP BY `Branch` HAVING COUNT(StudentID) > 2 ORDER BY NumofstudentsBranch  DESC;

above SQL statement selecting studentid and branch from student table to lists the number of student in each branch, sorted high to low as NumofstudentsBranch (descending order) by SQL HAVING Clause

Example(3)

SELECT student.StudentName, COUNT(course.CourseID) AS NumberOfCoures FROM (course INNER JOIN student ON course.CourseID = student.CourseID) GROUP BY StudentName HAVING NumberOfCoures > 1

SQL statement statement selecting student name from student table and courseid from course table to lists the student that have appers more than 1 by SQL HAVING Clause

2.Complete code of SQL SQL HAVING Clause in PHP script.

<!DOCTYPE html>
<html lang="en">
<head>
    <title>How to use SQL HAVING Clause</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
</head>
<body>
<div class="container">
    <div class="text-center">
        <h1>SQL HAVING Clause</h1>
    </div>
    <div class="well">
        <?php
        $connect = mysqli_connect("localhost", "root", "", "school_management");

        if ($connect->connect_error) {
            die("Connection failed: " . $connect->connect_error);
        }

        $sql_1 = "SELECT COUNT(StudentID) AS Numberofstudents , `Country` FROM student GROUP BY `Country` HAVING Numberofstudents  > 2";
        $sql_2 = "SELECT COUNT(StudentID) AS NumofstudentsBranch , `Branch` FROM student GROUP BY `Branch` HAVING COUNT(StudentID) > 2 ORDER BY NumofstudentsBranch  DESC";
        $sql_3 = "SELECT student.StudentName, COUNT(course.CourseID) AS NumberOfCoures FROM (course INNER JOIN student ON course.CourseID = student.CourseID) 
                    GROUP BY StudentName HAVING NumberOfCoures > 1";

        echo "<h4 class='text-danger'>Example(1)</h4>";
        if($result_1 = mysqli_query($connect, $sql_1)){
            if(mysqli_num_rows($result_1) > 0){
                echo "<table class='table'>
                    <tr>
                     <th>NumberofRecords</th>
                     <th>Country</th>
                    </tr>
                    ";
                while($row = $result_1->fetch_assoc()){
                    echo "<tr>
                   <td>".$row['Numberofstudents'] /*student table*/ ."</td> 
                   <td>".$row['Country']  ."</td>
                  </tr>";
                }
                echo "</table>";
            } else{
                echo "No records found.";
            }
        }
        echo "<h4 class='text-danger'>Example(2) </h4>";
        if($result_1 = mysqli_query($connect, $sql_2)){
            if(mysqli_num_rows($result_1) > 0){
                echo "<table class='table'>
                    <tr>
                     <th>Branch</th>
                     <th>NumofstudentsBranch</th>
                    </tr>
                    ";
                while($row = $result_1->fetch_assoc()){
                    echo "<tr>
                   <td>".$row['Branch'] /*student table*/ ."</td> 
                   <td>".$row['NumofstudentsBranch'] ."</td>
                  </tr>";
                }
                echo "</table>";
            } else{
                echo "No records found.";
            }
        }
        echo "<h4 class='text-danger'>Example(3)</h4>";
        if($result_1 = mysqli_query($connect, $sql_3)){
            if(mysqli_num_rows($result_1) > 0){
                echo "<table class='table'>
                    <tr>
                     <th>StudentName</th>
                     <th>NumberOfCoures</th>
                    </tr>
                    ";
                while($row = $result_1->fetch_assoc()){
                    echo "<tr>
                   <td>".$row['StudentName'] /*student table*/ ."</td> 
                   <td>".$row['NumberOfCoures']."</td>
                  </tr>";
                }
                echo "</table>";
            } else{
                echo "No records found.";
            }
        }
        ?>
    </div>
    <br>
</div>
</body>
</html>

Related Post