HAVING Clause in sql server

HAVING Clause

HAVING is used to trying to filter based on one of the grouping fields. It can be used in a SELECT statement to filter the records that a GROUP BY.

• HAVING clause is used to filter grouping fields.
• HAVING clause is used after GROUP BY clause.

 Following is employee Table where I will demonstrate the HAVING clause.

EMPNO	ENAME	JOB	SAL	DEPT	DEPTNO	DNAME	LOC
NULL	NULL	NULL	NULL	NULL	1	ACCOUNTING	ST LOUIS
NULL	NULL	NULL	NULL	NULL	2	RESEARCH	NEW YORK
2	Anil	Manager	12000	3	3	SALES	ATLANTA
3	Adam	Developer	6700	3	3	SALES	ATLANTA
5	Tina	Developer	3000	3	3	SALES	ATLANTA
1	Alok	Admin	18000	4	4	OPERATIONS	SEATTLE
4	Anit	Developer	4500	4	4	OPERATIONS	SEATTLE

Counting employee on department wise by using GROUP BY clause

SELECT DEPTNO, DNAME, COUNT(EMPNO) AS TotalEmployee FROM  EMP GROUP BY DEPTNO,DNAME

 OutPut:-

DEPTNO	DNAME	TotalEmployee
1	ACCOUNTING	0
4	OPERATIONS	2
2	RESEARCH	0
3	SALES	3

Fetching department that have at least one employee by using HAVING clause example

SELECT DNAME, COUNT(EMPNO) AS TotalEmployee FROM  EMP GROUP BY DNAME HAVING COUNT(EMPNO)>1

OutPut:-

DEPTNO	DNAME	TotalEmployee
4	OPERATIONS	2
3	SALES	3

 Fetching department that have no employee by using HAVING clause example

SELECT DNAME, COUNT(EMPNO) AS TotalEmployee FROM  EMP GROUP BY DNAME HAVING COUNT(EMPNO)<1

OutPut:- 

DEPTNO	DNAME	TotalEmployee
1	ACCOUNTING	0
2	RESEARCH	0

I am hoping that you are enjoying with this post! Please share with you friends. Thank you!!