Group By Clause in PHP – Info 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.

 

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

 

 

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.   
  2. $con=mysql_connect(“localhost”,“root”,“”);  
  3. if (!$con)  
  4.   {  
  5.   die(‘Could not connect: ‘ . mysql_error());  
  6.   }  
  7. mysql_select_db(“mysql”$con);  
  8. print 

    MySQL: Simple select statement

    ;  

  9. $result = mysql_query(“select * from emp_dtl”);    
  10. echo “‘1’>  

  11.   
  12. EmpId

      

  13. Firstname

      

  14. Lastname

      

  15. Role

      

  16. Salary

      

  17. “;  

  18. while($row = mysql_fetch_array($result))  
  19.   {  
  20.    echo 
    ;  
  21.   echo “ “ . $row[‘id’] . 

    ;  

  22.   echo “ “ . $row[‘Firstname’] . 

    ;  

  23.   echo “ “ . $row[‘Lastname’] . 

    ;  

  24.   echo “ “ . $row[‘role’] . 

    ;  

  25.     echo “ “ . $row[‘salary’] . 

    ;  

  26.   echo 

    ;  

  27.   }  
  28.   echo 

    ;  

  29.    
  30.     
  31.    
  32.   print 

    MySQL: Group by clause in PHP

    ;  

  33. $result = mysql_query(“select id, avg(salary)as totalsal from emp_dtl group by id”);   
  34. echo “‘1’>  

  35.   
  36. EmpId

      

  37. Salary

      

  38. “;  

  39. while($row = mysql_fetch_array($result))  
  40.   {  
  41.    echo 
    ;  
  42.   echo “ “ . $row[‘id’] . 

    ;  

  43.   echo “ “ . $row[‘totalsal’] . 

    ;  

  44.   echo 

    ;  

  45.   }  
  46.   echo 

    ;  

  47.    
  48.   print 

    MySQL: Group by with having clause

    ;  

  49. $result = mysql_query(“select id, count(*) as total from emp_dtl group by id having count(*)>1”);   
  50. echo “‘1’>  

  51.   
  52. EmpId

      

  53. Duplicate Records

    ;  

  54. “;  

  55. while($row = mysql_fetch_array($result))  
  56.   {  
  57.    echo 
    ;  
  58.   echo “ “ . $row[‘id’] . 

    ;  

  59.   echo “ “ . $row[‘total’] . 

    ;  

  60.   echo 

    ;  

  61.   }  
  62.   mysql_close($con);  
  63.   ?>  
  64.   
  65.   echo 

    ;  

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. 

serverpoint hosting banner

Article Prepared by Ollala Corp

You might also like More from author

Leave A Reply

Your email address will not be published.