SQL SERVER GROUP BY clause

SQL SERVER GROUP BY clause

GROUP BY clause  is used to group repeated data like dept number, year,job title,customer id, etc.,

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	12/17/1980 12:00:00 AM	800.00		20
7499	ALLEN	SALESMAN	7698	2/20/1981 12:00:00 AM	1600.00	300.00	30
7521	WARD	SALESMAN	7698	2/22/1981 12:00:00 AM	1250.00	500.00	30
7566	JONES	MANAGER	7839	4/2/1981 12:00:00 AM	2975.00		20
7654	MARTIN	SALESMAN	7698	9/28/1981 12:00:00 AM	1250.00	1400.00	30
7698	BLAKE	MANAGER	7839	5/1/1981 12:00:00 AM	2850.00		30
7782	CLARK	MANAGER	7839	6/9/1981 12:00:00 AM	2450.00		10
7839	KING	PRESIDENT		11/17/1981 12:00:00 AM	5000.00		10
7844	TURNER	SALESMAN	7698	9/8/1981 12:00:00 AM	1500.00	0.00	30
7900	JAMES	CLERK	7698	12/3/1981 12:00:00 AM	950.00		30
7902	FORD	ANALYST	7566	12/3/1981 12:00:00 AM	3000.00		20
7934	MILLER	CLERK	7782	1/23/1982 12:00:00 AM	1300.00		10

 

Ex:  get total  number of employees from each department 


select deptno,count(*) total from emp group by deptno

deptno    total
10    3
20    3
30    6

 

Ex: get number of employees in each job title.

 

select job,count(*) total from emp group by job

 

job    total
ANALYST    1
CLERK    3
MANAGER    3
PRESIDENT    1
SALESMAN    4

 

Ex: get average salary by job category

select job,avg(sal) 'avg salary' from emp group by job

job    avg salary
ANALYST    3000.000000
CLERK    1016.666666
MANAGER    2758.333333
PRESIDENT    5000.000000
SALESMAN    1400.000000

Ex: get order count for each year and month.

used Northwind Orders Table.

select Datepart(year,orderdate) Year,
Datepart(month,orderdate) month,
count(*) total from orders
group by Datepart(year,orderdate),Datepart(month,orderdate)
order by Datepart(year,orderdate),Datepart(month,orderdate)

1996    7    22
1996    8    25
1996    9    23
1996    10    26
1996    11    25
1996    12    31
1997    1    33
1997    2    29
1997    3    30
1997    4    31
1997    5    32
1997    6    30
1997    7    33
1997    8    33
1997    9    37
1997    10    38
1997    11    34
1997    12    48
1998    1    55
1998    2    54
1998    3    73
1998    4    74
1998    5    14

Tags:SQL SERVER GROUP BY clause ,SQL SERVEr DATE FUNCTION Datepart,SQL Server Aggregate function count