RE: sql with averages and medians

Mike,

Sorry there isn't a function like AVG that will calculate a median value for
you directly. Although you could probably write a UDF to do it. Here's
what I have used to calculate one...

select x.FIELDNAME from LIBRARY/FILE x, LIBRARY/FILE y
group by x.FIELDNAME
having sum(case when y.FIELDNAME <= x.FIELDNAME then 1 else 0
end)>=(count(*)+1)/2 and
sum(case when y.FIELDNAME >= x.FIELDNAME then 1 else 0 end)>=(count(*)/2)+1


The value returned is the median value of FIELDNAME in the file.

HTH

Tom Garvey

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Smith, Mike
Sent: Thursday, March 29, 2012 12:58 PM
To: 'Midrange Systems Technical Discussion'
Subject: sql with averages and medians

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?

Thanks

Mike
NOTICE: This message, including any attachment, is intended as a
confidential and privileged communication. If you have received this message
in error, or are not the named recipient(s), please immediately notify the
sender and delete this message.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.