Aggregate Functions on subsets of data based on current row values with SQL
- by aasukisuki
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?