Median - Need help writing code

[KristenC]

I've written the code below to come up with a median but it is not working. I've used this, modified a little, before & it worked OK but now my numbers aren't coming up at all. What I'm getting is actually 1/2 the value of the lowest number in the entire data set, not using my WHERE clause. Example...I have 200 rows in the table, the lowest value is 50,000. The WHERE clause should limit to 3 rows for this particular median price to 3 rows with a median of 250,000. What I'm getting is 25,000 (1/2 of the lowest value). PLEASE HELP!!!


DECLARE
--
CURSOR c_get_city IS
SELECT DISTINCT(city)
FROM lng_order;
--
v_city VARCHAR(50);
v_count NUMBER;
v_med_sale_price NUMBER(14);
v_med_sale_price_1 NUMBER(14);
v_med_sale_price_2 NUMBER(14);
v_updated NUMBER;
--
BEGIN
--
OPEN c_get_city;
--
LOOP
--
FETCH c_get_city
INTO v_city;
EXIT WHEN c_get_city%NOTFOUND;
--
SELECT COUNT(*)
INTO v_count
FROM lng_order
WHERE city = v_city;
--

DBMS_OUTPUT.PUTLINE

IF MOD(v_count,2) = 0
THEN
SELECT MAX(sale_price)
INTO v_med_sale_price_1
FROM (SELECT sale_price
FROM lng_order
WHERE city = v_city
AND rownum <= ((v_count/2) + 1.1));
--
SELECT MAX(sale_price)
INTO v_med_sale_price_2
FROM (SELECT sale_price
FROM lng_order
WHERE city = v_city
AND rownum <= ((v_count/2) + 0.1));
--
v_med_sale_price := (v_med_sale_price_1 + v_med_sale_price_2) / 2;
--
ELSE
SELECT MAX(sale_price)
INTO v_med_sale_price
FROM (SELECT sale_price
FROM lng_order
WHERE city = city
AND rownum <= ((v_count/2) + 0.6));
--
END IF;
--
UPDATE median_mkt_share
SET median_price = v_med_sale_price
WHERE city = v_city
AND broker_name = 'Long and Foster';
--
END LOOP;
--
CLOSE c_get_city;
--
END;
--
/

 

[sem]

You've probably missed an ORDER BY clause, so you're getting MAX(sale_price) from a half of arbitrary rows selected by city. Try smth like following:

/*Your declarations*/

cursor c_city_prices(p_city varchar2) is
SELECT sale_price
FROM lng_order
WHERE city = p_city
order by 1;



/*should be near DBMS_OUTPUT.PUTLINE*/
if v_count<>0
then
open c_city_prices(v_city);

for i in 1..trunc(v_count/2)
loop
fetch c_city_prices into v_med_sale_price1;
end loop;

if MOD(v_count,2) = 0
fetch c_city_prices into v_med_sale_price2;
v_med_sale_price := (v_med_sale_price1 + v_med_sale_price2)/2;
else
v_med_sale_price := v_med_sale_price1;
end if;

close c_city_prices;
else
v_med_sale_price := 0;
end if;

/*your code*/
UPDATE median_mkt_share
SET median_price = v_med_sale_price
WHERE city = v_city
AND broker_name = 'Long and Foster';
....

 

[sem]

Sorry, ROUND instead of TRUNC