GROUP BY CLAUSE IN SQL SERVER

Dept Table

Group by:

Is used to divide the rows of a table into groups and then calculate aggregate on each group separately.

Syntax:

Select * /<collist> from <tablename>

Where <condition>

Group by <collist>

[Order by…..]

               

Example:

Display max salaries for each department.

select deptno, max(sal) from emp group by deptno

Find total and average salary paid to employee?

select deptno, sum(sal) , avg(sal) from emp group by deptno

Find total salary paid to employee job-wise in each department.

selectdeptno,job,sum(sal) from emp group by deptno,job

Display max sal, min sal , sum sal, no of employees working in each job

selectjob, max(sal) maxsal, min(sal)  minsal, sum(sal) sumsal, count(*)  noofemp

fromemp group by job

Display no of employees joined in each year

selectyear(hiredate) year, count(*) noofemp from emp group by year(hiredate)

No of employees joined in each day.

selectdatename(dw,hiredate) day, count(*) noofemp from emp group by datename(dw,hiredate)

To find the highest salaries for each department in it for each job

selectdeptno,job ,max(sal) from emp group by deptno,job

To find no of employees working for each department

selectdeptno,count(*) from emp group by deptno

Display maxsalaries for each department whose department number is equal to 10 or 30

select deptno, max(sal) maxsal from emp where deptno in(10,30) group by deptno

Rule:

 A rule to follow while using group by is

1 Every column in select other than aggregates must be in group by