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>