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