| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
|
03-01-11, 09:52
|
|
Registered User
|
|
Join Date: Mar 2010
Posts: 32
|
|
|
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
|
|
03-01-11, 10:35
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
|
|
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 
|
|
03-01-11, 12:04
|
|
Registered User
|
|
Join Date: Mar 2010
Posts: 32
|
|
|
|
Quote:
Originally Posted by r937
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!
|
|
03-01-11, 12:22
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
|
|
Quote:
Originally Posted by dvdaddict32
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?
|
|
03-01-11, 13:55
|
|
Registered User
|
|
Join Date: May 2003
Location: USA
Posts: 5,198
|
|
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
|
|
03-01-11, 14:04
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,195
|
|
Quote:
|
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 14:08.
|
03-01-11, 14:15
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,195
|
|
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.
|
|
03-01-11, 14:53
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
|
|
Quote:
Originally Posted by tonkuma
... 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"
|
|
03-01-11, 15:10
|
|
Registered User
|
|
Join Date: Mar 2010
Posts: 32
|
|
Quote:
Originally Posted by r937
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
|
|
03-01-11, 15:12
|
|
Registered User
|
|
Join Date: Mar 2010
Posts: 32
|
|
Quote:
Originally Posted by tonkuma
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.
|
|
03-01-11, 16:03
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,195
|
|
Quote:
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 16:14.
|
03-01-11, 16:35
|
|
Registered User
|
|
Join Date: May 2003
Location: USA
Posts: 5,198
|
|
Quote:
Originally Posted by dvdaddict32
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
|
|
03-01-11, 16:42
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
|
|
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
|
|
03-01-11, 17:00
|
|
Registered User
|
|
Join Date: Mar 2010
Posts: 32
|
|
Quote:
Originally Posted by Marcus_A
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)
|
|
03-01-11, 19:06
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,195
|
|
Quote:
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 + 21 12 + 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.
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Need Help Averaging Highest Salaries
