Thanks Tom,
I'll take a look a this and give it a try.
Mike
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Thomas Garvey
Sent: Thursday, March 29, 2012 4:50 PM
To: 'Midrange Systems Technical Discussion'
Subject: 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.
--
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.
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.
midrange.com
