PostgreSQL Subquery: Display some information of all employees whose salary is above the average salary for their departments

Last update on October 27 2025 12:16:44 (UTC/GMT +8 hours)

14. Write a SQL subquery to find the employee ID, first name, last name and salary of all employees whose salary is above the average salary for their departments.

Sample Solution:

Code:

-- This SQL query retrieves the employee ID and first name of employees whose salary is greater than the average salary of employees in their respective departments.

SELECT employee_id, -- Selects the employee_id column from the employees table
       first_name -- Selects the first_name column from the employees table
FROM employees AS A -- Specifies the table from which to retrieve data (aliased as 'A'), in this case, the employees table
WHERE salary >( -- Filters the rows to include only those where the salary is greater than the average salary of employees in the same department
    SELECT AVG(salary) -- Subquery: Calculates the average salary of employees in the same department as the current employee (aliased as 'A')
    FROM employees 
    WHERE department_id = A.department_id -- Matches the department_id of the current employee (aliased as 'A') with the department_id in the subquery
);

Explanation:

This SQL query retrieves the employee ID and first name of employees whose salary is greater than the average salary of employees in their respective departments.
The outermost SELECT statement selects the employee ID and first name from the employees table (aliased as 'A').
The WHERE clause filters the rows to include only those where the salary of the employee (aliased as 'A') is greater than the average salary obtained from the subquery.
The subquery calculates the average salary of employees in the same department as the current employee (aliased as 'A').
The WHERE clause in the subquery matches the department_id of the current employee (aliased as 'A') with the department_id in the subquery.

Sample table: employees

Sample table: departments

Output:

pg_exercises=# SELECT employee_id, first_name
pg_exercises-# FROM employees AS A
pg_exercises-# WHERE salary >
pg_exercises-# ( SELECT AVG(salary)
pg_exercises(# FROM employees
pg_exercises(# WHERE department_id = A.department_id);
 employee_id | first_name
-------------+------------
         103 | Alexander
         104 | Bruce
         114 | Den
         100 | Steven
         141 | Trenna
         156 | Janette
         157 | Patrick
         158 | Allan
         162 | Clara
         122 | Payam
         123 | Shanta
         124 | Kevin
         137 | Renske
         174 | Ellen
         145 | John
         146 | Karen
         147 | Alberto
         148 | Gerald
         149 | Eleni
         150 | Peter
         151 | David
         184 | Nandita
         185 | Alexis
         188 | Kelly
         152 | Peter
         189 | Jennifer
         192 | Sarah
         193 | Britney
         120 | Matthew
         121 | Adam
         108 | Nancy
         109 | Daniel
         168 | Lisa
         169 | Harrison
         170 | Tayler
         201 | Michael
         205 | Shelley
(37 rows)

Go to:

PREV : Write a SQL subquery to find the employee ID, first name, last name and department names of all employees.
NEXT : Write a subquery to find the 5th maximum salary of all salaries.

Practice Online

Have another way to solve this solution? Contribute your code (and comments) through Disqus.