CODEWITHSUNDEEP
sqloracle-databasegroup-by
Zbarcea Christian
Zbarcea Christian

Reputation: 9548

Oracle - How to return average from a subquery?

I need to select employees having salary bigger than the average salary grouped by departments.

SELECT * FROM employees
WHERE salary > (SELECT AVG(salary), department_id FROM employees GROUP BY department_id)

It's failing because It returns me 2 columns.

I have tried with this query:

SELECT * FROM employees
HAVING salary > AVG(salary)
GROUP BY (department_id)

Now i am getting error message: ORA-00979: not a GROUP BY expression

Upvotes: 1

Views: 2657

Answers (1)

PinnyM
PinnyM

Reputation: 35533

The simplest cross-database approach would be to use a JOIN:

SELECT employees.*
FROM employees
JOIN ( SELECT department_id, AVG(salary) avgSalary
       FROM employees
       GROUP BY department_id) departmentSalaries
  ON employees.department_id = departmentSalaries.department_id
  AND employees.salary > departmentSalaries.avgSalary

The most efficient approach for Oracle would be to use an analytic function (aka window function):

SELECT * FROM (
  SELECT e.*, AVG(e.salary) OVER ( PARTITION BY e.department_id ) as avgSalary
  FROM employees e) t
WHERE salary > avgSalary

Upvotes: 3

Related Questions