Latest Post

Thursday, 31 August 2017

Group by Clause in sql server

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.· 
Following is employee Table where I will demonstrate the Group by 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!!

Government Jobs