Thursday, 31 August 2017

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!!

No comments:

Post a Comment