/    /  DBMS – Having Clause 

Having Clause 

 

The HAVING Clause allows users to specify filtering conditions that conclude which group results appear in the output. Since the WHERE keyword could not be used with aggregate functions, the HAVING clause was added to SQL. 

Syntax:

SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

 

Let us consider the following table R4 

 

 R4 : FACULTY-1

———————————————————— 

FNo FName              DNo Qual           Salary

————————————————————

522 Ria  21 Ph.D. 35000

524 Priya  22 MTech 30000

525 Sia  22 MTech 42000

527 Maggie 23 MTech 28000

530 Alex 23 MTech 32000

533 Ben 24 Ph.D.          33000

535 Becca  24 Ph.D.          32000

537 Justin  25 MTech 26000

539 Pat  25 MTech 24000

540 Jack 25 MTech 34000

————————————————————

 

Example-1 : Find the average salary in each department, having average salary greater than 30000.

 

   Select

DNo, avg(Salary)  as avg_sal

   From FACULTY-1

   Group by  DNo

   Having avg(Salary) > 30000 ;

  

   Output :  DNo  avg_sal

   21 35000

   22 36000

   24 32500

 

Example-2 : Find the departments, having at least three employees.

 

   Select  DNo, count(*)  as  no_emp

   From FACULTY-1

   Group by  DNo

   Having count(*) > 2 ;

  

   Output :  DNo  no_emp

 25 3

 

Reference

Having Clause