I have written a piece of code in Oracle database 19c to extract the salary of employees who earns more than the average salary of their respective departments but the query shows "missing right parenthesis". Can anybody help me with it, like where and what the error is all about?

select ename 
from emp 
where sal > (select round(avg(sal)) as avg_sal, deptno 
             from  emp 
             group by deptno 
             order by 2);

CodePudding user response：

You can use these queries to get job done.

select ename 
from emp e1 
where sal > (select round(avg(sal)) as avg_sal
             from  emp e2
             where e2.deptno = e1.deptno 
             );

or

select e1.ename 
from emp e1,
  (
  select round(avg(sal)) as avg_sal, deptno 
  from  emp 
  group by deptno
  ) e2
where e1.deptno = e2.deptno
and e1.sal > avg_sal
;

CodePudding user response：

Now the output of your subquery is 2 columns and multiple rows, which is against the business logic you described. To gain the desired result you need to modify the query a bit:

• remove the second column in subquery;
• remove GROUP BY and ORDER BY clauses from the subquery.

Should be as follows:

SELECT ename FROM emp WHERE sal > (SELECT round(AVG(sal)) AS avg_sal FROM emp);

Now subquery returns only one values which is valuated with each salary in the main query and returns employees' names of ones, that have salary more that average.