Group By Clause in PHP

Introduction

 

I have described in my previous article how aggregate functions work. Basically, aggregate functions provide all the data in a single result set. The aggregate functions group data together. To do that you can use two new clauses of the select statement, "group by" and "having" clauses.

 

Group by clause

 

The "group" clause is used to group the rows of a result set based on one or more column expressions. Group by clause determines how the selected rows are grouped. The group by clause follows the where clause. If you want, more than one column or expression will be included in the "group by" clause, separated by commas.

 

Syntax

SELECT columns Name....... from table Name
Where condition
Group by group_by_list
Having condition
Order by order_by_list

Example

 

 

Having clause

 

The "Having" clause determines which groups are included in the final result. The having clause comes after the "group by" clause. It will be called when the data is to be grouped.

 

I explain my point using an example. Suppose you have a table with the columns id, name, and salary. You can see that in the first image of this article, in which the emp_dtl table data is shown. The id 102 is duplicated.

 

Then when you apply: "select id, Firstname,avg(salary) as avgsal from emp_dtl group by id;"

 

The following will be the result:

 

 

Then, if you want the average salary of each group greater then 2600 then use the following query:

 

"select id, Firstname,avg(salary) as avgsal from emp_dtl group by id having(salary)>2600";

 

 

So, we say that a "Having" clause will be called when the data is grouped.

 

Example of "Group by" clause in PHP

<?php

$con=mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("mysql", $con);
print "<h2>MySQL: Simple select statement</h2>";
$result = mysql_query("select * from emp_dtl");  // First query
echo "<table border='1'>
<tr>
<th>EmpId</th>
<th>Firstname</th>
<th>Lastname</th>
<th>Role</th>
<th>Salary</th>
</tr>";
while($row = mysql_fetch_array($result))
  {
   echo "<tr>";
  echo "<td>" . $row['id'] . "</td>";
  echo "<td>" . $row['Firstname'] . "</td>";
  echo "<td>" . $row['Lastname'] . "</td>";
  echo "<td>" . $row['role'] . "</td>";
    echo "<td>" . $row['salary'] . "</td>";
  echo "</tr>";
  }
  echo "</table>";

  //Group by in PHP

  print "<h2>MySQL: Group by clause in PHP</h2>";
$result = mysql_query("select id, avg(salary)as totalsal from emp_dtl group by id"); // Second query
echo "<table border='1'>
<tr>
<th>EmpId</th>
<th>Salary</th>
</tr>";
while($row = mysql_fetch_array($result))
  {
   echo "<tr>";
  echo "<td>" . $row['id'] . "</td>";
  echo "<td>" . $row['totalsal'] . "</td>";
  echo "</tr>";
  }
  echo "</table>";

  print "<h2>MySQL: Group by with having clause</h2>";
$result = mysql_query("select id, count(*) as total from emp_dtl group by id having count(*)>1"); // Third query
echo "<table border='1'>
<tr>
<th>EmpId</th>
<th>Duplicate Records</th>;
</tr>";
while($row = mysql_fetch_array($result))
  {
   echo "<tr>";
  echo "<td>" . $row['id'] . "</td>";
  echo "<td>" . $row['total'] . "</td>";
  echo "</tr>";
  }
  mysql_close($con);
  ?>

  echo "</table>";

Note: In the above example, first the query "select * from emp_dtl" simply shows all the information of the emp_dtl table. And the second query "select id, avg (salary) as totalsal from emp_dtl group by id" is grouped by the id column data and the result is each group's average total salary. And the third "query select id, count(*) as total from emp_dtl group by id having count(*)>1" counts the number of duplicate records.

Output

Difference between Where clause and Having clause

 

The "Where" clause is called before rows are grouped. You cannot use aggregate functions with the "where" clause.

 

The "Having" clause is called after rows are grouped. In other words it is included with the final result. You can use aggregate functions with the "having" clause.