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

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)

Practice Online

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

Previous: 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.