Advertisement

Latest Post

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

Government Jobs