O'Reilly logo

SQL in a Nutshell, 3rd Edition by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Name

CORR

The CORR function returns the correlation coefficient between a set of dependent and independent variables.

ANSI SQL Standard Syntax

Call the function with two variables, one dependent and the other independent:

CORR(dependent, independent)

Any pair in which either the dependent variable, the independent variable, or both are NULL is ignored. The result of the function is NULL when none of the input pairs consists of two non-NULL values.

Oracle

Oracle supports the ANSI SQL syntax, and the following analytic syntax:

CORR(dependent, independent) OVER (window_ clause)

For an explanation of the window_clause, see the “ANSI SQL Window Functions” section later in this chapter.

PostgreSQL

PostgreSQL supports the ANSI SQL syntax of the CORR function.

MySQL and SQL Server

These platforms do not support any form of the CORR function.

Example

The following CORR example uses the data retrieved by the first SELECT:

SELECT * FROM test2;
         Y          X
---------- ----------
         1          3
         2          2
         3          1

SELECT CORR(y,x) FROM test2;
CORR(Y,X)
---------
       −1

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required