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.·
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 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
|
--Fetchin
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:-
DNAME
|
TotalEmployee
|
OPERATIONS
|
2
|
SALES
|
3
|
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.