Getting the Mean, Median and Mode in Oracle

I've used the avg function forever (although sometimes I think it's named AVERAGE), but I needed to determine the mode of a bunch of numbers today and came across the STATS_MODE function which is quite nice.

Here's an example...


SQL> create table test (my_num number);

Table created.

SQL> insert into test values (1);

1 row created.

SQL> insert into test values (1);

1 row created.

SQL> insert into test values (2);

1 row created.

SQL> insert into test values (2);

1 row created.

SQL> insert into test values (2);

1 row created.

SQL> insert into test values (4);

1 row created.

SQL> insert into test values (5);

1 row created.

SQL> insert into test values (6);

1 row created.

SQL> insert into test values (7);

1 row created.

SQL> insert into test values (8);

1 row created.

SQL> select avg(my_num) from test;

AVG(MY_NUM)
--------------
3.8

SQL> select median(my_num) from test;

MEDIAN(MY_NUM)
--------------
3

SQL> select STATS_MODE(my_num) from test;

STATS_MODE(MY_NUM)
------------------
2



Until next time...Rich

Comments

Rich said…
btw... STATS_MODE returns the value with the greatest frequency (found the most times)....
Braden said…
You're awesome. Thanks much.
dKazuma Sez said…
WOW... I'm looking for mode function and your post really help me much. Thx.