SQL Reference

CORR

CORR is a function in SQL that returns the coefficient of correlation of a set of number pairs. It is used to measure the statistical relationship between two variables. The resulting value will always lie between -1 and 1, inclusive, with a positive number indicating a positive correlation and a negative number indicating a negative correlation. A value of 0 indicates no correlation.

CORR(expr1, expr2)

expr1: This parameter is the first set of numeric values that Oracle’s CORR function will consider during calculation. It can refer to column name, constant value, expression, or function call. Non-numeric values get discarded and paired values with null are excluded from correlation calculation.
expr2: Similarly, this is the second set of numeric values in the correlation calculation. It also accepts column names, constant values, expressions, or function calls. Non-numeric values are excluded and pairs that include null value from either expr1 or expr2 are removed from the correlation computation.

Example

SELECT CORR(sales, profit) as correlation
FROM sales_profit_data;

Output

CORRELATION
----------------
0.89734

Explanation

In the given example, the CORR function of Oracle SQL is used to find the correlation coefficient of sales and profit from the sales_profit_data table. The correlation coefficient is a statistical measure that calculates the strength of the relationship between the relative movements of the two variables. The value returned, 0.89734, indicates a strong positive relationship between sales and profit.

CORR(Y, X)

y: This parameter represents the first variable in the correlation function. In the context of CORR(Y, X) in PostgreSQL, “Y” would represent the first set of numeric data that you would like to compare.
x: This parameter represents the second variable in the correlation function. In relation to CORR(Y, X), “X” would depict the second set of numeric data that you are looking to compare with “Y”.

Example

SELECT CORR(column1, column2)
FROM table;

Output

0.345789

Explanation

The corr() function is used to return the coefficient of correlation of a set of number pairs. The returned result is between -1 and 1. A positive number signifies a positive correlation and a negative number signifies a negative correlation.