Advertisement

Latest Post

Thursday, 31 August 2017

What is difference between Where, Group by and HAVING clause

Following is employee Table where I will demonstrate the Where, Group by and 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

Where Clause-
WHERE can be used clause with SELECT, INSERT, UPDATE and DELETE statement to filtering rows and it applies on each and every row.
  • WHERE clause is used before GROUP BY clause.
  • We can't use aggregate functions in the where clause
Fetching all employee that belongs to DEP 3
SELECT * FROM  EMP WHERE EMP.DEPT=3

OutPut:-
EMPNO
ENAME
JOB
SAL
DEPT
DEPTNO
DNAME
LOC
2
Anil
Manager
12000
3
3
SALES
ATLANTA
3
Adam
Developer
6700
3
3
SALES
ATLANTA
5
Tina
Developer
3000
3
3
SALES
ATLANTA

Group by Clause-
Group by is used to group the result set or we can say that gather all the rows together on the based on the one or more columns. It allows aggregate functions to be performed on the one or more columns.
  • To use Group by Clause, we need to use at least one aggregate function
  • We can use Group by Clause with or without Where Clause.
-- Counting employee in each department by using GROUP BY clause
SELECT DNAME, COUNT(EMPNO) AS TotalEmployee FROM GROUP BY DNAME

OutPut:-
DNAME
TotalEmployee
ACCOUNTING
0
OPERATIONS
2
RESEARCH
0
SALES
3

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