How to Find the Employees having same Salary By Department in SQL Server

SQL Sub Query with Count(*) Function, Over(Partition By..) Clause to Find the Employees having Same Salary By Department

Suppose we have the Employee and Department tables as follows..

Select * From [dbo].[Tbl_EmpDetails]

Select * From [dbo].[Tbl_Dept]

Now our objective here is to find and select the Employees who are drawing Same Salary.

We achieve that using the Count (*) Function with Over (Partition By..) Clause in Subquery as discussed below.

1) Employees Drawing the Same Salary :

We can find the Employees drawing the Same Salary by using the following Subquery on the Employee table as per below.

SELECT Emp_Id, EmpName, NetSal From
 (
 SELECT Emp_Id,EmpName, NetSal, Count(*) Over (Partition by NetSal) as SalaryCnt
 FROM [dbo].[Tbl_EmpDetails]
 ) S1
WHERE SalaryCnt>1

ORDER By NetSal Desc

Result :

2) Employees Drawing the Same Salary with Dept Details :
We can find the Employees drawing the Same Salary, with their Department Details using the following Subquery on the Employee and Department Tables as below.

SELECT S1.Emp_Id,S1.EmpName,S1.DeptName, S1.NetSal
FROM(Select E.*, D.DeptName,Count(*) Over (Partition by E.NetSal) as SalaryCnt
            From [dbo].[Tbl_EmpDetails] E
   Inner Join [dbo].[Tbl_Dept] D
   ON E.Dept_Id=D.DeptId
  ) S1
WHERE S1.SalaryCnt > 1
ORDER By S1.NetSal Desc

Result:

3) Employees Drawing the Same Salary By Department :
We can find the Employees drawing the Same Salary By Department using the following Subquery on the Employee and Department Tables as below.

SELECT S1.Emp_Id,S1.EmpName,S1.DeptName, S1.NetSal

From (Select E.*, D.DeptName,

                     Count(*) Over (Partition by D.DeptName,E.NetSal) as SalaryCnt

          From [dbo].[Tbl_EmpDetails] E
   Inner Join [dbo].[Tbl_Dept] D
   ON E.Dept_Id=D.DeptId
  ) S1
WHERE S1.SalaryCnt > 1
ORDER By S1.NetSal Desc

Result:

Note:

Since No two Employees in the IT Department is having the Same Salary so that that record has been excluded.

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------