Aggregate Functions on subsets of data based on current row values with SQL
Posted
by aasukisuki
on Stack Overflow
See other posts from Stack Overflow
or by aasukisuki
Published on 2010-05-15T00:55:02Z
Indexed on
2010/05/15
1:04 UTC
Read the original article
Hit count: 319
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?
© Stack Overflow or respective owner