More complex median calculation in Oracle SQL

Please consider the following example:

CREATE TABLE TMP_ARTICLES
(
  ARTICLE_ID NUMBER,
  UNIT_PRICE NUMBER,
  SOLD_UNITS NUMBER
)
;

INSERT INTO "TMP_ARTICLES" (ARTICLE_ID, UNIT_PRICE, SOLD_UNITS) VALUES
('1', '10', '100');

INSERT INTO "TMP_ARTICLES" (ARTICLE_ID, UNIT_PRICE, SOLD_UNITS) VALUES
('2', '20', '110');

INSERT INTO "TMP_ARTICLES" (ARTICLE_ID, UNIT_PRICE, SOLD_UNITS) VALUES
('3', '30', '120');

INSERT INTO "TMP_ARTICLES" (ARTICLE_ID, UNIT_PRICE, SOLD_UNITS) VALUES
('4', '40', '130');

INSERT INTO "TMP_ARTICLES" (ARTICLE_ID, UNIT_PRICE, SOLD_UNITS) VALUES
('5', '50', '140');

INSERT INTO "TMP_ARTICLES" (ARTICLE_ID, UNIT_PRICE, SOLD_UNITS) VALUES
('6', '60', '150');

INSERT INTO "TMP_ARTICLES" (ARTICLE_ID, UNIT_PRICE, SOLD_UNITS) VALUES
('7', '70', '160');

INSERT INTO "TMP_ARTICLES" (ARTICLE_ID, UNIT_PRICE, SOLD_UNITS) VALUES
('8', '80', '170');

INSERT INTO "TMP_ARTICLES" (ARTICLE_ID, UNIT_PRICE, SOLD_UNITS) VALUES
('9', '90', '180');

INSERT INTO "TMP_ARTICLES" (ARTICLE_ID, UNIT_PRICE, SOLD_UNITS) VALUES
('10', '100', '190');

How do I determine the median of the unit prices "x" in the following meaning:

My total turnover of articles with an unit price lower or equal to x is more than fifty percent of my total turnover, but the total turnover of all unit prices with any unit price y < x is less than fity percent of my total turnover.

In the example above x = 80 because:

select sum(unit_price * sold_units) as total_turnover from tmp_articles;

=> My total turnover is 88000

select

     sum(unit_price * sold_units) as total_turnover from

     tmp_articles
where

     unit_price <= 80;

=> 52800 (more than 50% of 88000)

select

     sum(unit_price * sold_units) as total_turnover from

     tmp_articles
where

     unit_price <= 79.99999;

=> 39200 (less than 50% of 88000).

I am not sure if "median" is the correct term for what I am looking for and I am not an expert for statistics. However, is there an oracle function to do what I want?

Thanks for you help.

Best regards,

Hans Received on Wed Aug 25 2010 - 10:44:30 CDT