Saturday, December 15, 2018

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
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.