Post
Ask Question

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
  1. SELECT columns Name....... from table Name   
  2. Where condition  
  3. Group by group_by_list  
  4. Having condition  
  5. Order by order_by_list  
Example
 
group-by-clause-in-mysql.jpg
 
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:
 
group-by-with-having1-clause-in-mysql.jpg
 
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";
 
group-by-with-having2-clause-in-mysql.jpg
 
So, we say that a "Having" clause will be called when the data is grouped.
 
Example of "Group by" clause in PHP
  1. <?php  
  2.   
  3. $con=mysql_connect("localhost","root","");  
  4. if (!$con)  
  5.   {  
  6.   die('Could not connect: ' . mysql_error());  
  7.   }  
  8. mysql_select_db("mysql", $con);  
  9. print "<h2>MySQL: Simple select statement</h2>";  
  10. $result = mysql_query("select * from emp_dtl");  // First query  
  11. echo "<table border='1'>  
  12. <tr>  
  13. <th>EmpId</th>  
  14. <th>Firstname</th>  
  15. <th>Lastname</th>  
  16. <th>Role</th>  
  17. <th>Salary</th>  
  18. </tr>";  
  19. while($row = mysql_fetch_array($result))  
  20.   {  
  21.    echo "<tr>";  
  22.   echo "<td>" . $row['id'] . "</td>";  
  23.   echo "<td>" . $row['Firstname'] . "</td>";  
  24.   echo "<td>" . $row['Lastname'] . "</td>";  
  25.   echo "<td>" . $row['role'] . "</td>";  
  26.     echo "<td>" . $row['salary'] . "</td>";  
  27.   echo "</tr>";  
  28.   }  
  29.   echo "</table>";  
  30.    
  31.   //Group by in PHP  
  32.    
  33.   print "<h2>MySQL: Group by clause in PHP</h2>";  
  34. $result = mysql_query("select id, avg(salary)as totalsal from emp_dtl group by id"); // Second query  
  35. echo "<table border='1'>  
  36. <tr>  
  37. <th>EmpId</th>  
  38. <th>Salary</th>  
  39. </tr>";  
  40. while($row = mysql_fetch_array($result))  
  41.   {  
  42.    echo "<tr>";  
  43.   echo "<td>" . $row['id'] . "</td>";  
  44.   echo "<td>" . $row['totalsal'] . "</td>";  
  45.   echo "</tr>";  
  46.   }  
  47.   echo "</table>";  
  48.    
  49.   print "<h2>MySQL: Group by with having clause</h2>";  
  50. $result = mysql_query("select id, count(*) as total from emp_dtl group by id having count(*)>1"); // Third query  
  51. echo "<table border='1'>  
  52. <tr>  
  53. <th>EmpId</th>  
  54. <th>Duplicate Records</th>;  
  55. </tr>";  
  56. while($row = mysql_fetch_array($result))  
  57.   {  
  58.    echo "<tr>";  
  59.   echo "<td>" . $row['id'] . "</td>";  
  60.   echo "<td>" . $row['total'] . "</td>";  
  61.   echo "</tr>";  
  62.   }  
  63.   mysql_close($con);  
  64.   ?>  
  65.   
  66.   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


group-by-clause-in-php.jpg

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. 


Similar Articles