CODEWITHSUNDEEP
sqlsql-servert-sqlselect
Miraziz
Miraziz

Reputation: 509

Perform SELECT statement inside of SELECT Statement?

I'm using Emp, Dept... databases. I would like to get the Name, Salary, Deptno and Average salary in the department of those employees who earn more than the average of their department. So here is what I'm trying to do: 

SELECT e.Ename, e.Sal, e.Deptno
  , (
    SELECT AVG(Sal) 
    FROM Emp b 
    WHERE b.Deptno = e.Deptno
    GROUP BY Deptno
  ) AS 'Average Salary'
FROM Emp e
WHERE e.Sal > (
  SELECT AVG(b.Sal)
  FROM Emp b
  WHERE b.Deptno = e.Deptno
  GROUP BY Deptno
);

And I can't use AVG(Sal), because it will give the average salary of the employee, and not the department where he works.

Upvotes: 0

Views: 98

Answers (3)

Uueerdo
Uueerdo

Reputation: 15941

If you join to the subquery, you will not need to repeat it.

SELECT e.Ename, e.Sal, e.Deptno, dAvgs.avgSal AS 'Average Salary'
FROM Emp AS e
INNER JOIN (
   SELECT Deptno, AVG(b.Sal) AS avgSal
   FROM Emp b
   GROUP BY Deptno
) AS dAvgs
ON e.Deptno = dAvgs.Deptno AND e.Sal > dAvgs.avgSal
;

Upvotes: 0

Kevin
Kevin

Reputation: 7309

Just get rid of the group by.

SELECT e.Ename, e.Sal, e.Deptno, (SELECT AVG(Sal) 
                                   FROM Emp b 
                                   WHERE b.Deptno = e.Deptno
                                   ) AS 'Average Salary'
FROM Emp e
WHERE e.Sal > (SELECT AVG(b.Sal)
                FROM Emp b
                WHERE b.Deptno = e.Deptno
                );

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You would use a correlated subquery:

SELECT e.Ename, e.Sal, e.Deptno,
       (SELECT AVG(e2.Sal) 
        FROM Emp e2
        WHERE e2.Deptno = e.Deptno
       ) AS [Average Salary]
FROM Emp e
WHERE e.Sal > (SELECT AVG(e2.Sal)
               FROM Emp e2
               WHERE e2.Deptno = e.Deptno
              );

But in actuality, you would just use a window function:

select e.*
from (select e.*, avg(sal) over (partition by deptno) as avg_sal
      from emp e
     ) e
 where sal > avg_sal;

Upvotes: 3

Related Questions