OVER(PARTITION BY) IN SQL SERVER

Dept Table

over partition by

A drawback of group by is that it can display the data from only the columns that are in group by

To overcome this problem sql server provides over(partition by---) 

Similarly to group by over partition is use to divide the rows into partitions and then calculate 

Aggregates on each partitions separately.

Syntax:

Select * /<collist>,<aggregate> over(partition by <collist>).  <aggregate> over(partition by <collist>) from <tablename>

[where <condition>   [order by  …..]

Examples:

Find total salary paid to employee dept-wise while displaying complete details of emp

Select *, sum (sal) over (partition by deptno) from EMP

Find the highest and lowest salary paid employee 

jobwise while displaying complete details of emp

Select *,max(sal) over(partition by job), min(sal) over(partition by job) from emp

Find the total salary paid to emp jobwise in each dept while displaying complete details

Select *, sum(sal) over(partition by deptno, job) from emp