Query to Find Department Wise MAX Salary of Employee

Introduction

In this article, I am going to explain how to write a query to find the department-wise max salary of employees where the salary of all employees are the same for a particular department. I will also explain how to write a subquery in SQL server as well as joining of the self table in SQL server and much other interesting stuff to writing a query.

This is a very common requirement, while we working with data-driven applications. Yesterday I got the requirement to write a query to find the maximum salary for each department and display a null value where all employees of a particular department having the same salary. Many developers of beginners may didn't know how to compare and check the records within a table or may didn't know how to write a sub-query so, today in this article I'll explain everything with an example.

In my previous article, I explained how to Split Alphabets from Alphanumeric String In SQL Server and Split Numbers From Alphanumeric String In SQL Server and How to Create Dynamic PIVOT Query in SQL Server and Concatenate value from multiple rows to a single string in SQL Server and How to Find the Last Date of Any Month in SQL Server

Requirement 

1) Write a SQL query to find the department wise maximum salary of the employees.

2) If any department having the same salary for all employees then display null value for that department.

So, let's create a sample table from employees and departments with dummy data for demonstration purposes.

Create a table for Department Master

CREATE TABLE DepartmentMaster
(
	Department_ID INT,
	DepartmentName VARCHAR(50)
)

Insert Data into Department Master

INSERT INTO DepartmentMaster(Department_ID,DepartmentName)
VALUES (1,'Product Development'),
	   (2,'Network and Security'),
	   (3,'Sales and Marketing')

Create a table for Employee Master

CREATE TABLE EmployeeMaster
(
	Employee_ID INT,
	EmployeeName VARCHAR(50),
	Salary DECIMAL,
	Department_ID INT
)

Insert Data into Employee Master

INSERT INTO EmployeeMaster (Employee_ID,EmployeeName,Salary,Department_ID)
VALUES(1001,'Nikunj Satasiya', 40000,1),
	  (1002,'Pritesh Dudhatra', 40000,1),
	  (1003,'Vinod Prajapati', 40000,1),
	  (1004,'Hiren Dobariya', 40000,1),
	  (1005,'Shreya Patel', 28000,2),
	  (1006,'Vivek Ghadiya', 34000,2),
	  (1007,'Reshma Patel', 10000,3),
	  (1008,'Dhara Savaliya', 10000,3),
	  (1009,'Krunal Patel', 10000,3)

So, now we will write a query to get all departments with a maximum salary of the employee and set null value for departments having the same salary for all employees.

Query

SELECT  
D.DepartmentName,
CASE WHEN T3.Department_ID IS NULL THEN MAX(E.Salary) ELSE NULL END As MaxSalary
FROM EmployeeMaster E
LEFT JOIN  DepartmentMaster D ON E.Department_ID = D.Department_ID
		LEFT JOIN (
				SELECT CASE WHEN T1.TotalEmployee =T2.TotalEmployee THEN T1.Department_ID ELSE NULL END AS Department_ID
				FROM (
					SELECT COUNT(1) AS TotalEmployee,
					Department_ID
					FROM EmployeeMaster
					GROUP BY Department_ID
			) AS T1
			LEFT JOIN (
				SELECT COUNT(1) As TotalEmployee, Department_ID 
				FROM
				 (
					SELECT Employee_ID,
					EmployeeName,
					Salary, 
					Department_ID,
					Count(*) Over (Partition by Department_ID, Salary) as SalaryCnt
					FROM EmployeeMaster
				 ) S1
				WHERE SalaryCnt>1
				GROUP BY Department_ID
			) T2 ON T1.Department_ID = T2.Department_ID
	) T3 ON D.Department_ID = T3.Department_ID
GROUP BY T3.Department_ID,D.Department_ID, D.DepartmentName
ORDER BY D.Department_ID

Explanation

As per the input data, In the employee master table, the "Product Development" department and "Sales and Marketing" department having the same salary for all employees.

As you can see in the query above, I have written subquery T2 that returns the count of employees having the same salary, Department_ID, and Salary wise. subquery T1 returns the department-wise total count of employees and finally, I have compared the result set of subquery T1 and T2 using the case when statement and check if T1.TotalEmployee =T2.TotalEmployee then select Department_ID from subquery T1 else select null. Finally, this query will return only department id having the same salary for all employees.

Next, the whole statement T3 I have used as a left join with employee master table and department master table. Finally using the case when statement checks for T3.Department_ID if it is NULL then use get maximum salary using aggregate function MAX(Salary) else set the value as NULL for maximum salary as well as also select other required columns and used that columns in the group by clause.

Here, I have used subqueries, but you can use temp table or CTE if you want to avoid multiple subqueries for better query performance. I have also written an article on basic tips for query performance tuning techniques that you might like to read.

Expected Output

Summary

In this article, we learned how to write a query to find the department wise maximum salary with the help of subquery.