Re: More complex median calculation in Oracle SQL

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Thu, 26 Aug 2010 06:08:24 -0700 (PDT)
Message-ID: <b6465ba8-e6aa-413c-86ca-32d6e8bf2378_at_f42g2000yqn.googlegroups.com>

On Aug 25, 11:44 am, Hans Mayr <mayr1..._at_gmx.de> wrote:
> Hello,
>
> 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

What version of Oracle?

With 10g+ there is a median function.

SQL> select median(unit_price) MEDUPRICE, median(sold_units) MEDSOLD   2 from tmp_articles;

 MEDUPRICE MEDSOLD
---------- ----------

        55 145

HTH -- Mark D Powell -- Received on Thu Aug 26 2010 - 08:08:24 CDT