PostgreSQL Query to Find Employees with Salaries Higher Than Their Departmental Average

In fact, in any organization, pay gaps exist among staff who are placed in different job categories based on their level of experience, skills, and negotiating power. On the other hand, when more employees make more than their department’s average, it tempts one to ask about fairness and equity.

In this article, we will discover how to use PostgreSQL to identify those employees gaining salaries higher than their departmental averages and examine some of the management implications and actions.

Understanding the Data

Let’s begin by looking into the dataset structure. We’ll assume a relational database schema with tables for employees and departments. Every employee record consists of fields: employee ID, name, department ID, and salary. Similarly, the department’s table holds information about department IDs and names.

We’ll need to perform a series of SQL queries to identify employees with salaries higher than their departmental averages. We will do these in four steps:

1. Create tables and insert data into them.
2. Calculate the departmental average salaries.
3. Join the result with the employee’s table.
4. Find out employees whose salaries are higher than their departmental averages.

Step 1 – Create tables named departments and employees and insert data into them.

Query:

CREATE TABLE departments (
    department_id SERIAL PRIMARY KEY,
    department_name VARCHAR(100)
);

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    department_id INT,
    salary NUMERIC(10, 2),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

INSERT INTO departments (department_name) VALUES
    ('HR'),
    ('Finance'),
    ('Marketing');

INSERT INTO employees (name, department_id, salary) VALUES
    ('Minal Pandey', 1, 60000.00),
    ('Vardhana Sharma', 1, 65000.00),
    ('Kavya Sharma', 2, 70000.00),
    ('Soni Pandey', 2, 75000.00),
    ('Mahi Pandey', 3, 62000.00),
    ('Abhilekh Pandey', 3, 68000.00),
    ('Vivek Sharma', 3, 71000.00);

Output:

You can see the content of both the table below:

Employees Table:

Employees Table

Departments Table:

Department Table

Step 2 – Calculate the departmental average salaries.

Query:

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

Output:

Department average salaries

Explanation: The output presents the department IDs along with their average salaries computed from employee data. HR’s average salary is 62,500, Finance’s is 72,500, and Marketing’s is 67,000. This information provides insight into salary distributions across different departments within the organization.

Step 3 – Join the above result with the employees table to associate each employee with their departmental average.

Query:

SELECT e.employee_id, e.name, e.salary, d.department_id, d.avg_salary
FROM employees e
JOIN (SELECT department_id, AVG(salary) AS avg_salary
      FROM employees
      GROUP BY department_id) d
ON e.department_id = d.department_id;

Output:

Result after Joining tables

Explanation: The output presents employee details alongside their respective department IDs and salaries. Additionally, it includes the average salary for each department. This provides a comparison between individual employee salaries and the average salary within their respective departments, aiding in identifying discrepancies or outliers in salary distributions.

Step 4 – Filter out employees whose salaries exceed their departmental averages.

Query:

SELECT *
FROM (
    SELECT e.employee_id, e.name, e.salary, d.department_id, d.avg_salary
    FROM employees e
    JOIN (SELECT department_id, AVG(salary) AS avg_salary
          FROM employees
          GROUP BY department_id) d
    ON e.department_id = d.department_id
) AS subquery
WHERE salary > avg_salary;

Output:

Employees

Explanation: The output shows employees earning more than the average salary in their department. It includes their ID, name, salary, department ID, and the department’s average salary. This comparison reveals employees earning higher-than-average wages within their departments, giving a snapshot of salary discrepancies.

Conclusion

Through the implementation of these SQL queries in PostgreSQL, it will be more feasible to recognize employees getting more than their departmental averages. This analysis provide managerial executives valuable insights to review and assess salary structures, deal with variations, and promote fairness and transparency. Furthermore, these parameters can be utilized in the process of developing a culture of equity and workforce satisfaction which is also very important.