SQL Server Group By Clause

SQL Server Group By
                 --> groups repeated data in a column(s)/Aggregates data


Note: grouping on non-repeated columns such as Primary key/unique key will result entire rows in the table.

Syntax:
           select col1,col2,col3  from table
           group by col1,col2,col3.

syntax 2:
            select col1, max(col2) from table
            group by col1.
       

Here is the Emp table

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

Note:In this table deptno and job columns have repeated values. Which are meaningful in-context.

Grouping Data on Single Column(DeptNo)

--Get Employee Count for each department

select deptno, count(*) Employees from emp
group by Deptno

deptno    Employees
10    3
20    3
30    6


--Get Employee max salary for each department

select deptno, Max(sal) "Max Salary" from emp
group by Deptno

deptno    Max Salary
10    5000.00
20    3000.00
30    2850.00

Grouping Data on Multi-Column(DeptNo,JOB)

--Get Employee count from each department based on job titleselect deptno, job,count(*) Employees from emp
group by Deptno,job
 
deptno    job    Employees
20    ANALYST    1
10    CLERK    1
20    CLERK    1
30    CLERK    1
10    MANAGER    1
20    MANAGER    1
30    MANAGER    1
10    PRESIDENT    1
30    SALESMAN    4

--Get Employee Max Salary in each department and job.
select deptno, job,Max(sal) "Max Salary" from emp
group by Deptno,job
 
deptno    job    Employees
20    ANALYST    3000.00
10    CLERK    1300.00
20    CLERK    800.00
30    CLERK    950.00
10    MANAGER    2450.00
20    MANAGER    2975.00
30    MANAGER    2850.00
10    PRESIDENT    5000.00
30    SALESMAN    1600.00
 

 Note: All columns specified in the select list(except those in aggregate functions)  should be part of group by clause.

otherwise Msg 8120, Level 16, State 1, Line 18
Column 'emp.[]' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


Tags: SQL Server Group By Clause, Group by Examples, Group by on single column, Group By on Multiple Column