Peter Larsson Blog

Patron Saint of Lost Yaks

How to calculate the covariance in T-SQL

DECLARE @Sample TABLE
        (
            x INT NOT NULL,
            y INT NOT NULL
        )

INSERT  @Sample
VALUES  (3, 9),
        (2, 7),
        (4, 12),
        (5, 15),
        (6, 17)

;WITH cteSource(x, xAvg, y, yAvg, n)
AS (
        SELECT  1E * x,
                AVG(1E * x) OVER (PARTITION BY (SELECT NULL)),
                1E * y,
                AVG(1E * y) OVER (PARTITION BY (SELECT NULL)),
                COUNT(*) OVER (PARTITION BY (SELECT NULL))
        FROM    @Sample
)
SELECT  SUM((x - xAvg) *(y - yAvg)) / MAX(n) AS [COVAR(x,y)]
FROM    cteSource

Legacy Comments


dmSQL
2012-10-24
re: How to calculate the covariance in T-SQL
Thanks. It seems that you only need OVER() rather than a PARTITION clause also.