April 18, 2014

MSSQL Statistical Z-Score

Computing the z-score of individual values in SQL Server is not a built in function (although average and standard deviation are). The z-score tells us how far off from the average value the individual values are.
The following function computes the z-score: for a table named [data] having an [objectId] and [score] fields.

SELECT
    [D].[objectId],
    [D].[score],
    [E].[avg_score],
    [E].[stdev_score],
    ([D].[score] - [E].[avg_score]) / [E].[stdev_score] as zvalue
FROM
    [data] D
    CROSS JOIN
    (SELECT
        Avg(CAST([score] as float)) as [avg_score],
        StDevP([score]) as [stdev_score]
    FROM [data]
    ) E
;