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
Note: only a member of this blog may post a comment.