TomBalla
asked on
need to get an average
I am trying to get the average pay for a group of people, but the way the database is setup everytime someone gets a raise it is a new entry, so someone could have 1 entry or 20. This is screwing up my average because it is taking the average of what they originally got paid through what they are making now and I just need their latest pay rate.
For instance this is what I see
Joe Mc $8
Joe Mc $8.50
Joe Mc $10
John Cour $9
Average = $8.88 where it should be $9.50. Joe should only count the one time.
For instance this is what I see
Joe Mc $8
Joe Mc $8.50
Joe Mc $10
John Cour $9
Average = $8.88 where it should be $9.50. Joe should only count the one time.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
mlmcc,
I don't think that will work. The sub-query will include each different pay rate, so you're still getting an average for all of the rates. It will include the maximum date with each rate, but that doesn't help (if anything, it makes things more confusing, since all of the rates now have the same date). I think you need another level. At the bottom level, find the maximum date, then find the rate for that date, then do the average.
Also, FWIW, in your query and angelIII's, the ")" should not be at the end of the second line.
James
I don't think that will work. The sub-query will include each different pay rate, so you're still getting an average for all of the rates. It will include the maximum date with each rate, but that doesn't help (if anything, it makes things more confusing, since all of the rates now have the same date). I think you need another level. At the bottom level, find the maximum date, then find the rate for that date, then do the average.
Also, FWIW, in your query and angelIII's, the ")" should not be at the end of the second line.
James
YOu are right. Need to check for max date in the query.
mlmcc
mlmcc
If so you could use that to find the current pay.
Same basic query
Open in new window
mlmcc