Re: How do I get the median value ?

Hello All My apologies for posting a wrong SP yesterday. Elliot Shoichet was kind enough to point my error out to me. The original SP had an implicit assumption that the dataset must be ordered which is of course impossible to ensure and thus was a wrong assumption. I have a new SP at the bottom of this post today which will work for unordered data sets as well. BTW Elliot raised the question about the difference between the financial median and the statistical median. According to Joe Celko's book "SQL for Smarties" (where the SP has been adapted from -- guess I am not such a smarty after all, huh? :-)), the statistical median has to be a member of the dataset, so in case of even numbered datasets there are two medians, the left and the right median. In case of the financial median, the median would be the average of the left and the right median in this case. There was a solution using a SCROLL cursor which IMHO would be the most efficient provided the client tool supported SCROLL cursors. So here is the SP. There is inline documentation to explain what it does. It uses TEMP tables so to call it repeatedly within a session one must do a CLOSE DATABASE and DATABASE databasename between each call to it. HTH Sujit ---------------------------- cut here ------------------------------------ CREATE PROCEDURE myMedian() RETURNING DECIMAL(5,2); DEFINE r_median DECIMAL(5,2); -- Create TEMP table work1. -- w1_trans_value = Unique transaction value -- w1_occurs = Number of times the value occurs in dataset SELECT trans_value w1_trans_value, COUNT(*) w1_occurs FROM trans GROUP BY trans_value INTO TEMP work1; -- Create TEMP table work2. -- w2_trans_value = Unique transaction value -- w2_occurs = Number of times the value occurs in dataset -- w2_el_before = Number of elements before the value is added to dataset -- w2_el_after = Number of elements after the value is added to dataset SELECT b.w1_trans_value w2_trans_value, b.w1_occurs w2_occurs, SUM(a.w1_occurs)-b.w1_occurs w2_el_before, SUM(a.w1_occurs) w2_el_after FROM work1 a, work1 b WHERE a.w1_trans_value <= b.w1_trans_value GROUP BY 1, 2 INTO TEMP work2; -- This will isolate one value if the number of values in the dataset -- is odd and since AVG(1 value) = the value this is the median. If -- the number of values in the dataset is even it will isolate the -- middle 2 values and take the average of that. SELECT AVG(w2_trans_value) INTO r_median FROM work2 -- Condition for odd number of rows in dataset. Will return only -- one row. WHERE (w2_el_after > (SELECT MAX(w2_el_after)/2 FROM work2) AND w2_el_before < (SELECT MAX(w2_el_after)/2 FROM work2)) -- Condition for even number of rows in dataset. Will take the -- average of the middle 2 rows. OR w2_el_before = (SELECT MAX(w2_el_after)/2 FROM work2) OR w2_el_after = (SELECT MAX(w2_el_after)/2 FROM work2); RETURN r_median; END PROCEDURE; ---------------------------- cut here ------------------------------------