Find the Most Frequent Values

To find the most frequent values, we can use STATS_MODE function. The following query shows areacode in state Missouri.

SQL> select areacode from T_PHONE_AREA where state='Missouri' order by 1;
  AREACODE
----------
       314
       314
       314
       314
       314
       314
       314
       314
       314
       314
       314
       314
       417
       417
       573
       573
       573
       636
       636
       636
       636
       636
       636
       660
       816
       816
       816
       816
       816
       816
       816
       816
       816
       816

34 rows selected.

In the following query, stats_mode(areacode) returns the areacode 314 that is the most frequent value.

SQL> select stats_mode(areacode) from T_PHONE_AREA where state='Missouri';

STATS_MODE(AREACODE)
--------------------
                 314

Calculate Standard Deviation Using SQL

Problem

How do we calculate the standard deviation of a variable, such as salary in the following table, using SQL?

SQL> select employee_id, department_id, hire_date, salary 
from EMPLOYEES where rownum <=20;

EMPLOYEE_ID DEPARTMENT_ID HIRE_DATE   SALARY
----------- ------------- --------- --------
        100            90 17-JUN-87  24000.0
        101            90 21-SEP-89  17000.0
        102            90 13-JAN-93  17000.0
        103            60 03-JAN-90   9000.0
        104            60 21-MAY-91   6000.0
        105            60 25-JUN-97   4800.0
        106            60 05-FEB-98   4800.0
        107            60 07-FEB-99   4200.0
        108           100 17-AUG-94  12000.0
        109           100 16-AUG-94   9000.0
        110           100 28-SEP-97   8200.0
        111           100 30-SEP-97   7700.0
        112           100 07-MAR-98   7800.0
        113           100 07-DEC-99   6900.0
        114            30 07-DEC-94  11000.0
        115            30 18-MAY-95   3100.0
        116            30 24-DEC-97   2900.0
        117            30 24-JUL-97   2800.0
        118            30 15-NOV-98   2600.0
        119            30 10-AUG-99   2500.0

20 rows selected.

Solution

We can use function stddev() to calculate the standard deviation.

SQL> select stddev(salary) from EMPLOYEES;

STDDEV(SALARY)
--------------
    3909.36575

The result can be verified using the following query.

SQL> with tbl as (select avg(salary) avg_sal, 
count(*) n from employees) 
select sqrt(sum((salary-avg_sal)*(salary-avg_sal)/(n-1))) 
from EMPLOYEES , tbl;

SQRT(SUM((SALARY-AVG_SAL)*(SALARY-AVG_SAL)/(N-1)))
--------------------------------------------------
                                        3909.36575