?
Solved

need to get an average

Posted on 2013-02-01
4
Medium Priority
?
197 Views
Last Modified: 2013-02-04
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
Comment
Question by:TomBalla
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1290 total points
ID: 38843882
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 38845882
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
 
LVL 35

Expert Comment

by:James0628
ID: 38846592
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 38847002
YOu are right.  Need to check for max date in the query.

mlmcc
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question