CORR Function in Oracle SQL – PLSQL

The CORR Function in Oracle SQL / PLSQL is used to calculate the coefficient of correlation in a set of numbers.

Syntax for the using the CORR Function in Oracle SQL / PLSQL is:

SELECT CORR(n,m)[OVER analytic_clause]
FROM table_name;

  • n and m are numbers used to calculate the coefficient of correlation.
  • The CORR function in Oracle SQL / PLSQL requires at least 2 records or rows in a table.

Example:

Using CORR Function in Oracle SQL / PLSQL SELECT statement:

Suppose we have a table named ‘employee’ as shown below:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10
106 Emp F 40000 Sales 10
107 Emp G 12000 Sales 10
108 Emp H 12000 Support

If we write our CORR Function query as:

SELECT CORR(salary, commission)
FROM employee;

We will get ‘-0.174844651545059’ as output.


Tagged , , , , , , , , , , , . Bookmark the permalink.