Simply put, order your values lowest to highest, calculate the number of rows and divide by 2 rounding up if the answer is not a whole number.
For example, the list of numbers below from lowest to highest, compute the median number by calculating the number of values 9 dividing by 2 = 4.5 which rounds up to 5. The median is the fifth value in this set of numbers. An odd number of total items in a list is the easiest to find the median. 3 4 5 6 7 8 9 10 11 (9 numbers) The median value in this list is 7. An even number of total items in a list is more difficult. There are 2 numbers in the middle of this list (5 and 6) 1 2 3 4 5 6 7 8 9 10 (10 numbers) The median is 5.5 ((5+6=11)/2)=5.5 It is up to you to decide rounding up or down which median is more significant. In the code below the median is rounded down, therefore being more conservative. Nice work Dennis ! -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Dennis McGrath Sent: Monday, December 07, 2009 12:32 PM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Calculate the Median If anyone is interested, this is what I would do: --Calulate the Median of a list of numbers DROP TABLE TempMedian CREATE TEMP TABLE TempMedian (TempNumber DOUBLE, TempOrder INT) INSERT INTO TempMedian (TempNumber) SELECT Weight FROM QuoteControl + WHERE ControlType IS NOT NULL AND LIMIT = 10000 CREATE INDEX TempNumber ON TempMedian (TempNumber) -- for sorting AUTONUM TempOrder IN TempMedian USING 1 1 ORDER BY TempNumber NUM CREATE INDEX TempOrder ON TempMedian (TempOrder) -- for selecting SET VAR vHowMany INTEGER SET VAR vMidOrder1 INTEGER SET VAR vMidOrder2 INTEGER SET VAR vMiddleNumber1 DOUBLE SET VAR vMiddleNumber2 DOUBLE SET VAR vMedian DOUBLE SELECT COUNT(*) INTO vHowMany FROM TempMedian SET VAR vMidOrder1 = (INT(.vHowMany/2)) SELECT TempNumber INTO vMiddleNumber1 FROM TempMedian + WHERE TempOrder = .vMidOrder1 IF (MOD(.vHowMany,2)) = 0 THEN --even SET VAR vMidOrder2 = (.vMidOrder1 + 1) SELECT TempNumber INTO vMiddleNumber2 FROM TempMedian + WHERE TempOrder = .vMidOrder2 SET VAR vMedian = ((.vMiddleNumber1 + .vMiddleNumber2)/2) ELSE --odd SET VAR vMedian = .vMiddleNumber1 ENDIF RETURN ________________________________________ From: [email protected] [mailto:[email protected]] On Behalf Of Fitts, David A. Sent: Monday, December 07, 2009 9:01 AM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Calculate the Median Hi, I don't think there is a median function but I think you can use use the (max(list)) and (min(list)) functions to get the highest and lowest values and then just divide by 2. Regards, Dave Fitts State of Maine Risk Management Division ________________________________________ From: [email protected] [mailto:[email protected]] On Behalf Of Roberts, Jennifer Sent: Monday, November 30, 2009 12:36 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Calculate the Median Hello, Does anyone know if RBASE has a function to calculate the median of a list of values? I do not see one in my help but that doesnt mean I havent overlooked it. ? Thank you, Jennifer Roberts Business Applications Specialist Ohio Masonic Home Phone: 937.525.3080 Fax: 937.505.4009 Ext: 5788 This electronic message contains information from the Ohio Masonic Home, its operating subsidaiaries or affiliates. The information may be confidential, privileged or otherwise protected from disclosure. The information is intended to be received solely by [email protected], for use as designated in the message. If you are not the designated recipient, you are instructed not to review, disclose, copy, distribute orotherwise use of this transmission or its contents. If you have received this transmission in error, please notify the Ohio Masonic Home immediately at [email protected]; in addition, please delete your record of transmission. Sending, receiving, downloading, displaying, printing material that is harassing, fraudulent, offensive, intimidating, defamatory, or otherwise unlawful or inappropriate is strictly prohibited by The Ohio Masonic Home.
