Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2009
    Posts
    46

    Unanswered: Need Help Calculating an Average Salary

    Hey guys,

    I am trying to create a query that will calculate each individual's average salary. The tricky part is I only want to average each individual's top 3 highest salaries in the history table.

    I came close with this query:

    SELECT mbr_ssn_nbr,
    Floor(AVG(mbr_sal_amt)) as average_salary
    from dsnp.pr01_t_mbr_hist
    group by mbr_ssn_nbr
    order by average_salary desc
    fetch first 3 rows only



    The problem here is that this query takes an average of each individual's entire salary history, and then gives me the top three highest average salaries. I need to use the Fetch command to retrieve each individual's top 3 salaries and then average that amount.

    I'm sure it will have something to do with sub-queries which I'm not that comfortable with yet. Can anyone point me in the right direction? Thanks!

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Consider using OLAP functions. Something like
    Code:
    select avg(...) over (partition by ssn_number order by month desc rows 2 preceding)
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jan 2009
    Posts
    46
    I am completely new to the OLAP functions, and I can't get one to run without syntax errors. For example:

    select mbr_ssn_nbr,
    row_NUMBER() over (order by mbr_sal_amt desc) as test
    from dsnp.pr01_t_mbr_hist
    order by test


    I get An unexpected token "<END-OF-STATEMENT>" was found following ""

    What am I overlooking here?

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Try "...order by 2" or use a subselect. You cannot refer to column aliases (the "test") in the same subselect.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What platform(OS) and DB2 version did you used?

    Newer version(at least DB2 V9 for LUW) supports ORDER BY simple-column-name.

    simple-column-name
    Usually identifies a column of the result table.
    In this case, simple-column-name must be the column name of a named column in the select list.

Posting Permissions

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