Re: sql with averages and medians

On 29-Mar-2012 10:58 , Smith, Mike wrote:

I have the following sql statement
SELECT PE1DPT, PEMEMP, PEMNAM
, integer(floor(current_date - getdatesql(PEMBDT))/10000) as age
, integer(floor(current_date - getdatesql(PEMHDT))/10000) as YOS
FROM premp, prei1
WHERE PEMEMP = PE1EMP
and PEMEES = 'FR'
ORDER BY age desc

I want to create another sql statement that is based on the previous
but gives me
# of employees by dept(PE1DPT), Average age(AGE), Median Age(AGE),
Average Service(YOS) , and median service(YOS)

So I've got this.

SELECT PE1DPT
, count(*)
, avg(integer(floor(current_date - getdatesql(PEMBDT))/10000)) as age
, avg(integer(floor(current_date - getdatesql(PEMHDT))/10000)) as YOS
FROM premp, prei1
WHERE PEMEMP = PE1EMP
and PEMEES = 'FR'
GROUP BY pe1dpt

I can't seem to figure out how I might get the median for age and
yrs of service though.

Anyone know how to do this?

Having created a function PREjDdtMed for example [best by whatever naming implies the join of the two files and deriving Median values by Department; cardinality 1] which accepts as input a value of PE1DPT [a specific Department], RETURNS TABLE (pe1MedAge dec(4, 1), pe1MedYos dec(4, 1)), and performs the necessary work to generate those medians for the ages and years-of-service columns for the particular department, then the following query [using the given\current query as a CTE] should work:

with
agg (pe1dpt, empcnt, avgage, avgyos) as
(SELECT PE1DPT
, count(*)
, avg(integer(floor(current_date - getdatesql(PEMBDT))/10000)) as age
, avg(integer(floor(current_date - getdatesql(PEMHDT))/10000)) as YOS
FROM premp, prei1
WHERE PEMEMP = PE1EMP
and PEMEES = 'FR'
GROUP BY pe1dpt
)
select agg.*
, (select pe1medage from table( prejdptmed(agg.pe1dpt) ) as a )
, (select pe1medyos from table( prejdptmed(agg.pe1dpt) ) as h )
/* SQL0412 for ,(select pe1medage, pe1medyos from table( ... */
/* which may be allowed in newer releases? I did not check. */
from agg

FWiW I would encapsulate the join of those two files in a VIEW which is then referenced both in the above AGG CTE and in the PREJDPTMED UDTF. I would also use JOIN\ON syntax [instead of using the WHERE clause] to make the [implicit INNER] join more conspicuous.

There are MEDIAN examples in the IBM i InfoCenter which can be used as reference to implement in the create function [table language sql].

Regards, Chuck