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

On Aug 25, 11:44*am, Hans Mayr <mayr1... (AT) gmx (DOT) de> wrote:
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 --

On Aug 25, 8:44*am, Hans Mayr <mayr1... (AT) gmx (DOT) de> wrote:
I don't have time, unfortunately, to think through this all the way,
but I suspect what you are looking for is an analytic. See
http://orafaq.com/node/55 you probably want to sum your total turnover
and partition by 50% of that. Various examples
http://asktom.oracle.com/pls/asktom/...17329730362010

You may also be able to brute-force the sql without analytics by
putting the sum in a subquery, called by the various things you want
to show.

I don't remember much about statistics, except that I had to memorize
a dozen or so different definitions of mean, median and mode. I don't
think the Oracle median function is the one you want, as it divides
the number of values above and below equally.

jg
--
@home.com is bogus.
http://en.wikipedia.org/wiki/UVB-76

Hello,

Thanks for your answers.

Mark: No, those functions don't do what I want. However I use 10g.

Joel: Yes, you are right, this is a solution. However I thought there
might have been an easier solution than the following sql which's
first line is the answer I look for.

select
unit_price,
running_total / total as perc
from
(select
unit_price,
sum(total_turnover) over(order by unit_price ROWS UNBOUNDED
PRECEDING) running_total,
sum(total_turnover) over() total
from
(select
unit_price,
sum(unit_price * sold_units) total_turnover
from
tmp_articles
group by
unit_price
order by
unit_price
)
)
where
running_total / total >= 0.5
order by
1 ;

But on the other hand the sql is not so complicated for the very
specific thing I was looking for. I just thought that within
statistics such questions where very common and thus a predefined
function would exist.

Best,

Hans

On Aug 27, 12:09*am, Hans Mayr <mayr1... (AT) gmx (DOT) de> wrote:
That's true. People who do serious statistics often have third party
software to do the specific things they need. Oracle analytics is
catching up, plus with newer standards of SQL more can be pushed into
the database engine. On the other hand, sometimes pushing things into
the engine hits up against limitations within the engine. Since those
limitations are evolving (like what Exadata can do, like
http://kerryosborne.oracle-guy.com/2...orage-indexes/
), this could be an exciting time for actuaries. And you know what
they say about actuaries - someone has to make accountants look
exciting.

It's hard to overcome inertia of a mature market. But that's why
Larry runs around buying companies, some of whom are past their
prime. There's some predictions floating around about what Larry will
buy next ( http://www.softwareadvice.com/articl...-next-1080310/
), I think the stats market has perhaps been overlooked, overshadowed
by BI. Or maybe it is just too open-source oriented (besides SAS) for
Larry to comprehend.

jg
--
@home.com is bogus.
http://projects.washingtonpost.com/t...ons/cyber-ops/