MainMenu

Home Java Overview Maven Tutorials

Sunday 15 January 2017

Difference between Group by and Having Clause With Example

Difference between Group by and Having Clause

Group By:

The GROUP BY clause will gather all of the rows together that contain data in the specified column(s) and will allow aggregate functions to be performed on the one or more columns.
Aggregate functions are like SUM,COUNT,MIN,MAX and AVG.


Example :

Consider the folowing table of Appriasal


Table Name : Appraisal
Emp_idAmount
E012000
E024000
E035000
E026000
E012000


Query :

So if you want to get employee record who got total appraisal in one year then your query will be :
Select Emp_id, SUM(Amount)
from Appraisal
Group by Emp_id.

Result :
Emp_idAmount
E014000
E0210000
E035000

Having: The HAVING clause is used in combination with the GROUP BY clause. It can be used in a SELECT statement to filter the records that a GROUP BY returns.

Group functions cannot be used in where.To restrict row by group function having clause is used.

Example :

Consider the folowing table of Appriasal


Table Name : Appraisal
Emp_idAmount
E012000
E024000
E035000
E026000
E012000


Query :

So Now if you want to get employee record who got total appraisal in one year more than 5000 then your query will be :
Select Emp_id, SUM(Amount)
from Appraisal
Group by Emp_id Having SUM(Amount)>5000.

Result :

Emp_idAmount
E0210000
Thanks :)

Tag:Difference between Group by and Having clause, Group by in sql with example, Having clause in Sql with example

No comments:

Post a Comment