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.

Setting Up an Environment

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 geeks

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

USE geeks

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

Create Table:

CREATE TABLE geeksforgeeks(
  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 'geeksforgeeks'
INSERT INTO geeksforgeeks (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 geeksforgeeks;

Output:

We can see our table is now ready to use.

Finding Departmental Average

Lets calculate the average salary from each department.

Query:

SELECT department, avg(salary) as average_salary 
from geeksforgeeks
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.

Finding all the Employees Whose Salaries are Higher Than those of their Respective Departments

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 geeksforgeeks g1
WHERE salary > (select avg(salary) from geeksforgeeks 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 geeksforgeeks g
JOIN (
    SELECT department, AVG(salary) AS avg_salary
    FROM geeksforgeeks
    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.

Conclusion

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.