SQL Reference - COVAR

COVAR_POP(expr1, expr2)

expr1: This is the first argument or expression for which the population covariance is to be calculated. It must be a number datatype or any type that can be implicitly converted to a number.
expr2: This parameter refers to the second argument or expression. Just like expr1, it also must be a number datatype or any type implicitly convertible to a number. The population covariance is calculated between expr1 and expr2.

Example

SELECT COVAR_POP(salary, commissions)
FROM employees;

Output

112233.44

Explanation

The COVAR_POP function is used for finding the population covariance for a pair of columns. In this example, the population covariance of salary and commissions is calculated from the employees table. It returns a single value.

COVAR_POP(Y, X)

y: This is the first set of paired data. It is a variable in the query that represents numeric, non-null values.
x: This is the second set of paired data. Similar to Y, X is a numeric variable in the query that also represents non-null values.

Example

SELECT COVAR_POP(column1, column2)
FROM table_name;

Output

0.0123456789

Explanation

In the provided example, the COVAR_POP() function is used to compute the population covariance of non-null pairs for column1 and column2 of table_name. Its output is a single numerical value, in this case, 0.0123456789.

COVAR_POP( expression1 , expression2 )

expression1: This is the first set of values, typically contained in a column, for which the function calculates the population covariance in SQL Server. The expression is any valid SQL Server expression of any one of the data types in the numeric category.
expression2: This is the second set of values to be included in the covariance calculation. It follows the same rules as expression1. The ordering of expression1 and expression2 in the function call doesn’t impact the result since covariance is commutative.

Example

CREATE TABLE test (a int, b int);
INSERT INTO test VALUES (1, 3), (2, 5), (4, 7), (5, 9);
SELECT COVAR_POP(a, b) FROM test;

Output

2.6875

Explanation

In the given example, a table named ‘test’ is created with two integer columns - ‘a’ and ‘b’. Four rows of data are inserted into this table. COVAR_POP(a, b) calculates the population covariance of columns ‘a’ and ‘b’.