Ranking with Aggregate function Replacing Group by in SQL

Ranking with Aggregate function Replacing Group by in SQL

As we all knows the Importance of Group by clause in SQL which is used for Grouping of Data by one or more Columns but with Ranking function we can Replace Group by in many Conditions.

--We will take an Example of Employee table

CREATE TABLE [dbo].[Employee1](
[Empid] [int] IDENTITY(1,1) NOT NULL,
[EmpName] [nvarchar](50) NULL,
[EmpSalary] [float] NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Employee1] ON
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (1, N'sumit', 5000)
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (2, N'sumit', 6000)
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (3, N'amit', 8000)
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (4, N'amit', 9000)
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (5, N'vijay', 10000)
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (6, N'vijay', 5000)
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (7, N'suresh', 5000)
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (8, N'suresh', 5000)
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (9, N'Mukesh', 130)
SET IDENTITY_INSERT [dbo].[Employee1] OFF

Select * from Employee1


So in the above table we can see that we have different salaries for same Employee so first we will apply
aggregate function with group by to get max salaries min salaries sum of Salaries count of salary Group by Employees.

--Query to Extract Employee Salaries(using aggregate functions Min,Max,Avg,Sum and Count) 
select EmpName,SUM(EmpSalary)TotalSalary,AVG(EmpSalary)AvgSalary,COUNT(EmpSalary)CountofSalary
,MIN(EmpSalary)MinSalary,MAX(EmpSalary) MaxSalary from employee1
group by EmpName 

Output:
EmpName TotalSalary AvgSalary CountofSalary MinSalary MaxSalary
amit 17000 8500 2 8000 9000
Mukesh 130 130 1 130 130
sumit 11000 5500 2 5000 6000
suresh 10000 5000 2 5000 5000
vijay 15000 7500 2 5000 10000

Now we will Replace Group by  Using Ranking function and achieve the same Result.

--Ranking Function Replacing Group by 

SELECT distinct EmpName
    ,SUM(EmpSalary) OVER(PARTITION BY EmpName) AS TotalSalary
    ,AVG(EmpSalary) OVER(PARTITION BY EmpName) AS AvgSalary
    ,COUNT(EmpSalary) OVER(PARTITION BY EmpName) As CountofSalary
    ,MIN(EmpSalary) OVER(PARTITION BY EmpName) AS MinSalary
    ,MAX(EmpSalary) OVER(PARTITION BY EmpName) AS MaxSalary
     ,SUM(EmpSalary) OVER(PARTITION BY [EmpDepartment]) AS TotalSalarybyDept
FROM employee1 

GO

Output:
EmpName TotalSalary AvgSalary CountofSalary MinSalary MaxSalary
amit 17000 8500 2 8000 9000
Mukesh 130 130 1 130 130
sumit 11000 5500 2 5000 6000
suresh 10000 5000 2 5000 5000
vijay 15000 7500 2 5000 10000


Using Ranking Function we get the same result which we were getting with Group By clause..