Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Mar 2010
    Posts
    32

    Unanswered: Need Help Averaging Highest Salaries

    I am trying to come up with a query that will take an employee's three highest salaries in their history table, and then report the average of these amounts.

    Unfortunately, our version of DB2 does not support RANK functions so I need an old school solution. I tried this query:


    select avg(A.mbr_sal_amt) as "salary"


    from

    (


    select mbr_ssn_nbr,
    mbr_hist_svc_cr_dt,
    mbr_sal_amt



    from dsnp.pr01_t_mbr_hist

    order by mbr_sal_amt desc
    fetch first 3 rows only ) as A




    But DB2 does not like the order by in my sub-query. I'm not sure if it would have even worked. I need to fetch the first 3 rows per SSN number, not the entire table.

    I just really don't see a way to do this. Can anyone help? A sample of the history table looks like this:



    mbr_ssn_nbr mbr_hist_svc_cr_dt mbr_sal_amt


    123457575 2011-01-01 432.77
    123457575 2011-02-01 233.11
    123457575 2011-03-01 111.34
    123666666 2011-01-01 234.66
    123666666 2011-02-01 237.77
    12366666 2011-03-01 123.55

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT mbr_ssn_nbr
         , AVG(mbr_sal_amt) AS salary
      FROM dsnp.pr01_t_mbr_hist AS t
     WHERE ( SELECT COUNT(*) 
               FROM dsnp.pr01_t_mbr_hist  
              WHERE mbr_ssn_nbr = t.mbr_ssn_nbr
                AND mbr_sal_amt > t.mbr_sal_amt ) < 3
    GROUP
        BY mbr_ssn_nbr
    untested
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2010
    Posts
    32
    Quote Originally Posted by r937 View Post
    Code:
    SELECT mbr_ssn_nbr
         , AVG(mbr_sal_amt) AS salary
      FROM dsnp.pr01_t_mbr_hist AS t
     WHERE ( SELECT COUNT(*) 
               FROM dsnp.pr01_t_mbr_hist  
              WHERE mbr_ssn_nbr = t.mbr_ssn_nbr
                AND mbr_sal_amt > t.mbr_sal_amt ) < 3
    GROUP
        BY mbr_ssn_nbr
    untested
    wow, that works. Would you mind explaining how this query works? I'm still a noob and not understanding it. Thanks for the help!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dvdaddict32 View Post
    Would you mind explaining how this query works?
    let's pretend you are standing in line at the bank

    count the number of people ahead of you

    if that number is less than three, then you are one of the first three people in line

    make sense?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You need to test it with this salary data for a given employee and make sure it works:

    2010 $76,000
    2009 $75,000
    2008 $74,000
    2007 $74,000
    2006 $73,000
    2005 $72,000
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    our version of DB2 does not support RANK functions so I need an old school solution.
    What are your DB2 version and platform?

    By the way, RANK would be an solution. See Marcus's sample data.
    Last edited by tonkuma; 03-01-11 at 15:08.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If you could find an answer using RANK for Marcus's sample data,
    it would give a suggestion of an answer using an old solution.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by tonkuma View Post
    ... it would give a suggestion of an answer using an old solution.
    what, you don't think my solution is correct?

    interesting sample data, by the way -- buddy got a raise from $74,000 to $74,000, an unlikely happenstance

    are you suggesting that we now quibble over whether the correct answer is (76+75+74)/3 or (76+75+74+74)/4

    by the way, which of these would dvdaddict be asking for? he wanted it based on "an employee's three highest salaries"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Mar 2010
    Posts
    32
    Quote Originally Posted by r937 View Post
    what, you don't think my solution is correct?

    interesting sample data, by the way -- buddy got a raise from $74,000 to $74,000, an unlikely happenstance

    are you suggesting that we now quibble over whether the correct answer is (76+75+74)/3 or (76+75+74+74)/4

    by the way, which of these would dvdaddict be asking for? he wanted it based on "an employee's three highest salaries"
    There should only be three numbers to be averaged so (76+75+74)/3 would be the correct one

  10. #10
    Join Date
    Mar 2010
    Posts
    32
    Quote Originally Posted by tonkuma View Post
    What are your DB2 version and platform?

    By the way, RANK would be an solution. See Marcus's sample data.
    Our programmer says we are on version 8. Oddly enough, I can do the FETCH function, but I get errors when I try to do RANK or DENSE_RANK. I tested the same RANK query on a SQL server, and it worked so I know it's not a syntax problem.

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If you could find an answer using RANK for Marcus's sample data,
    it would give a suggestion of an answer using an old solution.
    I was thinking as follwings...

    If you want (76 + 75 + 74) / 3 for Macus's sample data(1.e. 76, 75, 74, 74, 73, 72)
    and there was an answer using RANK on newer DB2 version or on anyother DBMS(like SQL server),
    the answer should include additional column(s) to mbr_sal_amt in ORDER BY clause of RANK() OVER(...), like...
    RANK() OVER(... ORDER BY mbr_sal_amt DESC, mbr_hist_svc_cr_dt)

    Then you may want to modify R937's code like this...

    Code:
    SELECT mbr_ssn_nbr
         , AVG(mbr_sal_amt) AS salary
      FROM dsnp.pr01_t_mbr_hist AS t
     WHERE ( SELECT COUNT(*) 
               FROM dsnp.pr01_t_mbr_hist  
              WHERE mbr_ssn_nbr = t.mbr_ssn_nbr
                AND (mbr_sal_amt > t.mbr_sal_amt
                     OR
                     mbr_sal_amt = t.mbr_sal_amt
                     AND
                     mbr_hist_svc_cr_dt < t.mbr_hist_svc_cr_dt
                    )) < 3
    GROUP
        BY mbr_ssn_nbr
    Last edited by tonkuma; 03-01-11 at 17:14.

  12. #12
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by dvdaddict32 View Post
    There should only be three numbers to be averaged so (76+75+74)/3 would be the correct one
    Are you saying that there are no duplicate salary numbers for a given employee in the data you are given?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT mbr_ssn_nbr
         , AVG(distinctsalary) AS salary
      FROM ( SELECT mbr_ssn_nbr
                  , mbr_sal_amt AS distinctsalary
               FROM dsnp.pr01_t_mbr_hist AS t
              WHERE ( SELECT COUNT(*) 
                        FROM dsnp.pr01_t_mbr_hist  
                       WHERE mbr_ssn_nbr = t.mbr_ssn_nbr
                         AND mbr_sal_amt > t.mbr_sal_amt ) < 3
             GROUP
                 BY mbr_ssn_nbr
                  , mbr_sal_amt ) AS d
    GROUP
        BY mbr_ssn_nbr
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Mar 2010
    Posts
    32
    Quote Originally Posted by Marcus_A View Post
    Are you saying that there are no duplicate salary numbers for a given employee in the data you are given?
    Sorry, yes there can be duplicate salary numbers. I would need the query to take the average of the top three highest distinct salaries. Doesn't matter what month they occur in.


    I have one account with this history:


    2009-12-31 3061
    2010-07-31 3061
    2010-12-31 3061
    2011-01-31 2112
    2010-08-31 2040
    2010-09-30 2040
    2010-10-31 2040

    R937's query is giving me 3061 for the average, but I need it to average (3061, 2122, 2040)

  15. #15
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I have one account with this history:


    2009-12-31 3061
    2010-07-31 3061
    2010-12-31 3061
    2011-01-31 2112
    2010-08-31 2040
    2010-09-30 2040
    2010-10-31 2040

    R937's query is giving me 3061 for the average, but I need it to average (3061, 2122, 2040)
    If I took values of sample data, answer must be
    (3061 + 2112 + 2040) / 3 = 2404

    (Added some data.)
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
    /****************************************
    ********* Start of sample data. *********
    ****************************************/
     pr01_t_mbr_hist
    (mbr_ssn_nbr , mbr_hist_svc_cr_dt , mbr_sal_amt) AS (
    VALUES
      (123457575 , '2009-12-31' , 3061)
    , (123457575 , '2010-07-31' , 3061)
    , (123457575 , '2010-12-31' , 3061)
    , (123457575 , '2011-01-31' , 2112)
    , (123457575 , '2010-08-31' , 2040)
    , (123457575 , '2010-09-30' , 2040)
    , (123457575 , '2010-10-31' , 2040)
    , (123457575 , '2011-02-28' , 1900)
    , (123457575 , '2010-05-31' , 1500)
    , (123666666 , '2011-01-01' ,  234)
    , (123666666 , '2011-02-01' ,  237)
    , (123666666 , '2011-03-01' ,  123) 
    )
    /****************************************
    *********   End of sample data. *********
    ****************************************/
    SELECT mbr_ssn_nbr
         , AVG  (DISTINCT mbr_sal_amt) AS avg_salary
     FROM  pr01_t_mbr_hist t
     WHERE (SELECT COUNT(DISTINCT mbr_sal_amt)
             FROM  pr01_t_mbr_hist
             WHERE mbr_ssn_nbr = t.mbr_ssn_nbr
               AND mbr_sal_amt > t.mbr_sal_amt
           ) < 3
     GROUP BY
           mbr_ssn_nbr
    ;
    ------------------------------------------------------------------------------
    
    MBR_SSN_NBR AVG_SALARY 
    ----------- -----------
      123457575        2404
      123666666         198
    
      2 record(s) selected.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •