Why is my SQL subquery returning an error in PostgreSQL when comparing salaries? [closed]

Question

Closed. This question needs debugging details. It is not currently accepting answers.

Edit the question to include desired behavior, a specific problem or error, and the shortest code necessary to reproduce the problem. This will help others answer the question.

Closed 9 months ago.

Improve this question

I'm trying to retrieve employees who earn more than the department's average salary using a subquery in PostgreSQL.

SELECT 
  employee_id, 
  first_name, 
  salary 
FROM 
  employees e 
WHERE 
  e.salary > (
    SELECT 
      AVG(salary) 
    FROM 
      employees 
    WHERE 
      department_id = e.department_id
  );

ERROR: more than one row returned by a subquery used as an expression

I checked if AVG(salary) is returning multiple values.

I attempted using GROUP BY department_id, but I received a different error.

I expected to get a list of employees who earn more than the average salary of their respective departments.

Instead, I got an error preventing the query from executing.

How can I correctly structure this query to compare each employee’s salary with their department’s average?

This ... department_id = e.department_id is causing this ... more than one row returned by a subquery used as an expression. In other words there is more then one department_id. Either make the subquery a CTE element or turn it into a JOIN with the other query. — Adrian Klaver
– Adrian Klaver, Commented Mar 21 at 21:47
I attempted using GROUP BY department_id, but I received a different error. - what error? Assuming you added this to the sub-query, it seems the correct thing to do. — Dale K
– Dale K, Commented Mar 22 at 2:25
Debug posts require a MRE--minimal reproducible example. How to Ask Help center Either ask about 1 coding error while giving a MRE or ask about 1 place you are stuck while giving relevant working parts & no code with errors (because it's not relevant). PS Clearly your error & your goal are longtime beginner duplicate Q&A that should not be asked yet again. How much research effort is expected of Stack Overflow users? But one must pin down via a minimal reproducible example & write many clear, concise & precise phrasings of one's question/problem/goal to search reasonably.How to Ask Help center — philipxy
– philipxy, Commented Mar 22 at 9:22
Debug questions require a minimal reproducible example--cut & paste & runnable code including initialization; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL include DDL & tabular initialization code. For debug that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. How to Ask Help center When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. (Debugging fundamental.) — philipxy
– philipxy, Commented Mar 22 at 9:26
Welcome to Stack Overflow! You haven't shown the definition of your table(s), without which it's hard to give a good answer. See Why should I provide an MCVE for what seems to me to be a very simple SQL query? — Toby Speight
– Toby Speight, Commented Mar 22 at 10:22

philipxy · Accepted Answer · 2025-03-22 09:24:03Z

1

Three ways:

create table employees (
  employee_id int,
  department_id int,
  first_name varchar(5),
  salary decimal(10,2)
)
;

insert into employees
values 
  (1, 1, 'bob', 123)
, (2, 1, 'jane', 234)
, (3, 2, 'john', 345)
;

with 
a as (
  select 
    employee_id
  , first_name
  , salary
  , AVG(salary) over (partition by department_id) as department_average_salary
  FROM employees e
)
select
  employee_id
, first_name
, salary
from a
where salary > department_average_salary
;

WITH
department_average_salary as (
  select 
    department_id
  , AVG(salary) as salary
  from employees
  group by department_id
)
select 
  e.employee_id
, e.first_name
, e.salary 
FROM employees e
  inner join department_average_salary a on a.department_id = e.department_id
where e.salary > a.salary
;

select 
  e.employee_id
, e.first_name
, e.salary 
FROM employees e
  inner join (
    select 
      department_id
    , AVG(salary) as salary
    from employees
    group by department_id
  ) a on a.department_id = e.department_id
where e.salary > a.salary
;

edited Mar 22 at 9:24

philipxy

15.2k6 gold badges44 silver badges101 bronze badges

answered Mar 21 at 22:33

dougp

3,1631 gold badge14 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Bohemian Mar 22 at 0:03

Sure, but OP asks why is the error occurring (not what are some alternative queries).

philipxy Mar 22 at 9:24

If the question isn't clear it shouldn't be answered.

philipxy Mar 22 at 12:29

@Bohemian The title asks about the error, but the post asks for a working query not why the error. Just yet another of the ubiquitous no-MRE no-thought no-reasoning code dump with ungoogled error message & either no question or asking for working code or for both working code & why the error. And here also a vague report of yet another try with absent bad code.

Collectives™ on Stack Overflow

Why is my SQL subquery returning an error in PostgreSQL when comparing salaries? [closed]

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related