Group By Clause in PHP – Info PHP

Introduction

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

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

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.