SQL Query to find Employees With Higher Salary than Their Department Average ?

Analyzing and understanding salary distributions within departments is a critical aspect of human resource management. In this article, we will explore SQL queries to calculate the average salary for each department and identify employees whose salaries exceed their departmental averages.

These queries provide valuable insights for performance evaluations and salary adjustment strategies. We will demonstrate two methods: one without using the JOIN() method and another using JOIN() for a comprehensive understanding. Let’s understand the practical examples to enhance your SQL skills and analytical capabilities in database management.

In this, we will create a database with a required table. We will cover all the basic steps to create a table in a database.

STEP 1: Let’s create a database.

CREATE DATABASE Beginner

STEP 2: After executing this query, we can see our database has been created. Now let’s use our database

USE Beginner

STEP 3: As we are done with creating the database, now let’s create a table in that database.

Create Table:

CREATE TABLE w3wiki(
  id INT PRIMARY KEY,
  name VARCHAR(10),
  department VARCHAR(10),
  salary INT
);

STEP 4: Now, we are done with creating a table with specified columns. Lets insert some values in our table and display them.

Insert Values in Table:

--inserting values in the table 'w3wiki'
INSERT INTO w3wiki (id, name, department, salary)
VALUES
    (101, 'Vishu', 'IT', 50000),
    (102, 'Sumit', 'HR', 40000),
    (103, 'Neeraj', 'HR', 25000),
    (104, 'Aayush', 'IT', 35000),
    (105, 'Harsh', 'IT', 32000),
    (106, 'Rahul', 'HR', 30000),
    (107, 'Vivek', 'IT',55000 );

-- displaying the table's data
   SELECT * FROM w3wiki;

Output:

We can see our table is now ready to use.

Lets calculate the average salary from each department.

Query:

SELECT department, avg(salary) as average_salary 
from w3wiki
GROUP BY department;

Output:

Explanation : We can clearly observe that average salaries are displayed with their corresponding department name. We can see that ‘IT’ department has average salary of 43000 and ‘HR’ department has 31666. This will also help us in verifying our data once we execute our nest query.

Method 1: Without using JOIN()

• In this method, we will create a query without using JOIN() method.
• We will create a subquery and a parent query.
• The subquery will return the departmental average and the parent query will display the employees details matching with result returned by the subquery or child query.

Query:

SELECT *
FROM w3wiki g1
WHERE salary > (select avg(salary) from w3wiki g2 where g2.department = g1.department);

Output:

Explanation: We know that ‘IT’ department has average salary of 43000 and ‘HR’ department has 31666. Therefor, all the names listed in the output block has salary higher than the average. The subquery return average salary from each department and parent query checks in the table whether any employee has salary higher than the average. If it does, then we will display all the details of the employees. Otherwise, we will display nothing.

Method 2: With using JOIN()

• In this method, we will more clearly explain our steps of execution. In this, we have used JOIN() method to join our queries.
• We have a subquery same as in the previous method. The subquery is will find the average salaries from each department.
• Then the parent query will evaluate the result, based on what it got from the child query or the subquery.

Query:

SELECT g.id, g.name, g.department, g.salary
FROM w3wiki g
JOIN (
    SELECT department, AVG(salary) AS avg_salary
    FROM w3wiki
    GROUP BY department
) AS g2 ON g.department = g2.department 
WHERE g.salary > g2.avg_salary;

Output:

Explanation: In the above query, we have used a similar kind of approach we have used in finding the departmental average. We have group each department and find the average salaries among them. Then the subquery will return the department name along with their respective salaries. Then the parent query will evaluate the result based on the result which it received from the child query. We have refer to the image, to see the smooth working of our query.

Overall, finding average salaries from each department as well as employees details those are receiving higher salaries than departmental average can help in many ways like performance of employees. This can also help in developing salaries adjustment strategies. We have covered two very easy and basic ways through which we can easily achieved the mentioned tasks. We have covered ways in which we have included JOIN() as well as without JOIN(). We have also covered how we can find the departmental average from the table. Now, have a good understanding of developing queries related to the topic and also some similar related queries.