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