How To Use SQL GROUP BY STATEMENT With Example In PHP

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

The GROUP BY Statement is used to arrange identical data into groups.if a particular column has same values in different rows then collect data across multiple data and group it by one or more columns.

Syntax SQL GROUP BY Statement.

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

The GROUP BY statement used for functions ( MAX, MIN,COUNT, SUM, AVG) to group the result-set by one or more columns.
The GROUP BY statement used for JOINS.

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

StudentID	StudentName	Address	Section	Branch	Percentage	CourseID
1	Liam	23 Andrew road	A	CSE	66	5
2	Andrew	2415 Anthony market str	A	ISE	80	5
5	Charles	878 Eli Berguvsvägen 8	C	ECE	52	9
9	Andrew	2415 Anthony market str	A	CE	80	2
10	Bhavya	18 Christopher sq.	B	CE	67	9
12	Rajesh	878 Eli Berguvsvägen 8	C	CL	52	3
13	Daniel	878 Eli Berguvsvägen 8	ME	CSE	52	8
15	Connor	878 Eli Berguvsvägen 8	C	ME	52	1
16	Kiran	878 Eli Berguvsvägen 8	ME	ECE	52	10

for above student table we will group branch column using SQL GROUP BY Statement.

Example(1)

SELECT COUNT(StudentID) As Numberofbranch , `Branch` FROM student GROUP BY `Branch`

above SQL statement grouping the repeated branch by studentID and fetch data as Numberofbranch from student table

Example(2)

SELECT student.Branch, COUNT(course.CourseID) 
AS NumberOfBranch FROM course LEFT JOIN student ON course.CourseID = student.CourseID GROUP BY StudentName

above SQL statement selecting branch from left side student table and course id from right side table by doing LEFT join data matches and return from course table and grouping the branch and fetch data as Numberofbranch from student table

2.Complete code of SQL GROUP BY Statement in PHP script.

<!DOCTYPE html>
<html lang="en">
<head>
    <title>SQL GROUP BY Statement </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 GROUP BY Statement</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 Numberofbranch , `Branch` FROM student GROUP BY `Branch`";
        $sql_2 = "SELECT student.Branch, COUNT(course.CourseID) 
                    AS NumberOfBranch FROM course LEFT JOIN student ON course.CourseID = student.CourseID GROUP BY StudentName";

        echo "<h2 class='text-danger'>SQL GROUP BY Statement with sql functions </h2>";
        if($result_1 = mysqli_query($connect, $sql_1)){
            if(mysqli_num_rows($result_1) > 0){
                echo "<table class='table'>
                    <tr>
                     <th>Numberofbranch</th>
                     <th>Branch</th>
                    </tr>
                    ";
                while($row = $result_1->fetch_assoc()){
                    echo "<tr>
                   <td>".$row['Numberofbranch'] /*student table*/ ."</td> 
                   <td>".$row['Branch'] /*Course table*/ ."</td>
                  </tr>";
                }
                echo "</table>";
            } else{
                echo "No records found.";
            }
        }
        echo "<h2 class='text-danger'>SQL GROUP BY Statement with left join </h2>";
        if($result_1 = mysqli_query($connect, $sql_2)){
            if(mysqli_num_rows($result_1) > 0){
                echo "<table class='table'>
                    <tr>
                     <th>Branch</th>
                     <th>NumberOfBranch</th>
                    </tr>
                    ";
                while($row = $result_1->fetch_assoc()){
                    echo "<tr>
                   <td>".$row['Branch'] /*student table*/ ."</td> 
                   <td>".$row['NumberOfBranch'] /*Course table*/ ."</td>
                  </tr>";
                }
                echo "</table>";
            } else{
                echo "No records found.";
            }
        }
        ?>
    </div>
    <br>
</div>
</body>
</html>

Related Post