How To Use SQL GROUP BY STATEMENT With Example In PHP

admin_img 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.

GROUP BY Statement

 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