Functions, 17 of 121

## CORR

#### Syntax For information on syntax and semantics, see "Analytic Functions".

#### Purpose

`CORR` returns the coefficient of correlation of a set of number pairs. You can use it as an aggregate or analytic function.

Both expr1 and expr2 are number expressions. Oracle applies the function to the set of (expr1 , expr2) after eliminating the pairs for which either expr1 or expr2 is null. Then Oracle makes the following computation:

```COVAR_POP(expr1, expr2) / (STDDEV_POP(expr1) * STDDEV_POP(expr2))

```

The function returns a value of type `NUMBER`. If the function is applied to an empty set, it returns null.

#### Aggregate Example

The following example calculates the coefficient of correlation between the salaries and commissions of the employees whose manager is 7698 from the EMP table:

```SELECT mgr, CORR(sal, comm) FROM EMP
GROUP BY mgr
HAVING mgr = 7698;

MGR CORR(SAL,COMM)
---------- --------------
7698     -.69920974
```

#### Analytic Example

The following example returns the cumulative coefficient of correlation of monthly sales and monthly profits from the `SALES` table for year 1998:

```SELECT s_month, CORR(SUM(s_amount), SUM(s_profit))
OVER (ORDER BY s_month) AS CUM_CORR
FROM sales
WHERE s_year=1998
GROUP BY s_month
ORDER BY s_month;

S_MONTH    CUM_CORR
---------- ----------
6
8          1
11 .860554259

```

Correlation functions require more than one row on which to operate, so the first row in the preceding example has no value calculated for it.