CORR

Oracle

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.

PostgreSQL

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.