Thursday, October 29, 2015

The Use of Over Function

CREATE TABLE [dbo].[Employees1]
[Id] [int] NOT NULL,
[Name] [nvarchar](50) NULL,
[Dept] [nvarchar](50) NULL,
[Gender] [nvarchar](50) NULL,
[Country] [nvarchar](50) NULL,
[Salary] [float] NULL,

Insert into Employees Values(1,'John','IT','Male','UK',5000)
Insert into Employees Values(2,'Mary','HR','Female','India',3000)
Insert into Employees Values(3,'Todd','IT','Male','UK',3500)
Insert into Employees Values(4,'Pam','HR','Female','India',4000)
Insert into Employees Values(5,'Tatcher','Payroll','Male','USA',2000)
Insert into Employees Values(6,'Sunil','IT','Male','USA',1400)
Insert into Employees Values(7,'Hari','Payroll','Male','UK',2500)
Insert into Employees Values(8,'Sunitha','HR','Female','India',4000)
Insert into Employees Values(9,'Sneha','IT','Female','India',3000)
Insert into Employees Values(10,'Ruby','Payroll','Male','UK',4600)

Select  Name, Salary, Employee.Gender,Genders.GenderTotal, Genders.AvgSal,Genders.MinSal,Genders.MaxSal
From Employees
Inner Join
(Select Gender,Count(*) as GenderTotal, AVG(Salary) as AvgSal, Min(Salary) as MinSalary, Max(Salary) as MaxSal
From Employees
Group By Gender) as Genders
On Genders.Gender = Employee.Gender

--Now use of Over Function

Select Name, Salary, Gender,
Count(Gender) OVER (Partition by Gender) as GenderTotal,
AVG(Salary) OVER (Partition by Gender) as AvgSal,
MIN(Salary) OVER (Partition by Gender) as MinSal,
MAX(Salary) OVER (Partition by Gender) as MaxSal
From Employees