Aggregate Functions on subsets of data based on current row values with SQL

Question

Hopefully that title makes sense...

Let's say I have an employee table:

ID | Name | Title   | Salary
----------------------------
1  | Bob  | Manager | 15285
2  | Joe  | Worker  | 10250
3  | Al   | Worker  | 11050
4  | Paul | Manager | 16025
5  | John | Worker  | 10450

What I'd like to do is write a query that will give me the above table, along with an averaged salary column, based on the employee title:

ID | Name | Title   | Salary | Pos Avg
--------------------------------------
1  | Bob  | Manager | 15285  | 15655
2  | Joe  | Worker  | 10250  | 10583
3  | Al   | Worker  | 11050  | 10583
4  | Paul | Manager | 16025  | 15655
5  | John | Worker  | 10450  | 10583

I've tried doing this with a sub-query along the lines of:

Select *, (select Avg(e2.salary) from employee e2 where e2.title = e.title) from employee e

But I've come to realize that the sub-query is executed first, and has no knowledge of the table alias'd e

I'm sure I'm missing something REALLY obvious here, can anyone point me in the right diretion?

Answer 1

SELECT e.Id
     , e.Name
     , e.Title
     , e.Salary
     , g.PosAvg
  FROM employee e
     , (SELECT e.Title
             , avg(Salary) PosAvg
          FROM employee e
       GROUP BY e.Title) g
   WHERE e.Title = g.Title