Average Sal of employee dept excluding his salary

The avg salary of peers expects the employee itself(that is, for each employee its peer would be everyone in the same department as the employee but excluding the employee himself).

We have two different tables .

I have tried some shit:

select   avg(salary.sal),emp.id,emp.dept from   emp   join salary on (emp.id <> salary.id )  group by emp.dept, emp.id;

but its not givig proper output.

Answer

You must self join the table to get the employee’s peers in the same department and then join to the table salary:

select 
  e.id,
  e.dept,
  avg(s.sal) averagesalary
from emp e 
inner join emp ee on ee.dept = e.dept and ee.id <> e.id
inner join salary s on s.id = ee.id
group by e.id, e.dept