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
Having Clause