Query to Find Department Wise MAX Salary of Employee

watch_later 2/01/2021


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.

Query to Find Department Wise MAX Salary of Employee

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


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.


FROM EmployeeMaster E
LEFT JOIN  DepartmentMaster D ON E.Department_ID = D.Department_ID
				SELECT CASE WHEN T1.TotalEmployee =T2.TotalEmployee THEN T1.Department_ID ELSE NULL END AS Department_ID
				FROM (
					SELECT COUNT(1) AS TotalEmployee,
					FROM EmployeeMaster
					GROUP BY Department_ID
			) AS T1
				SELECT COUNT(1) As TotalEmployee, Department_ID 
					SELECT Employee_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


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

Expected Output


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

Codingvila provides articles and blogs on web and software development for beginners as well as free Academic projects for final year students in Asp.Net, MVC, C#, Vb.Net, SQL Server, Angular Js, Android, PHP, Java, Python, Desktop Software Application and etc.

Thank you for your valuable time, to read this article, If you like this article, please share this article and post your valuable comments.

Once, you post your comment, we will review your posted comment and publish it. It may take a time around 24 business working hours.

Sometimes I not able to give detailed level explanation for your questions or comments, if you want detailed explanation, your can mansion your contact email id along with your question or you can do select given checkbox "Notify me" the time of write comment. So we can drop mail to you.

If you have any questions regarding this article/blog you can contact us on info.codingvila@gmail.com

sentiment_satisfied Emoticon