[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 202
  • Last Modified:

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.
0
TomBalla
Asked:
TomBalla
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
so, you have to first get the max() value for each, and then average on those, something along this sql code:
select average( max_pay)
  from ( select username, max(pay) as max_pay )
                from yourtable
            group by username
     ) subquery 

Open in new window


the code may need to be varied base on the  db engine

max(pay) may be wrong, if a user could also go "down" on the pay ...
in that case, you may need to consider other techniques, to get the max (or current) date for the pay to consider.
see this article:
http://www.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html
0
 
mlmccCommented:
Do you have a date field that indicates when the pay change occurred?

If so you could use that to find the current pay.

Same basic query

select average(pay)
  from ( select username, pay, Max(paydate) as max_paydate )
                from yourtable
            group by username
     ) subquery 

Open in new window


mlmcc
0
 
James0628Commented:
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
0
 
mlmccCommented:
YOu are right.  Need to check for max date in the query.

mlmcc
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now