Updated: 30 September 2010

*Note: This documentation is for the SQL2008 (and later) version of this XLeratorDB function, it is not compatible with SQL Server 2005.*

Click here for the SQL2005 version of the ZTEST function

Use multi-input aggregate ZTEST function to calculate the one-tailed probability of a Z-test. The Z-test is a statistical test used in inference which determines if the difference between a sample mean and the population mean is large enough to be statistically significant, that is, if it is unlikely to have occurred by chance. The Z-test is used primarily with standardized testing to determine if the test scores of a particular sample of test takers are within or outside of the standard performance of test takers.

SELECT [wctStatistics].[wct].[ZTEST] (

<@Known_x, float,>

,<@mu, nvarchar(4000),>

,<@Sigma, float,>)

the x-values supplied to the function. *@Known_x* is an expression of type **float** or of a type that can be implicitly converted to **float**.

the value at which to evaluate the function. *@mu* is an expression of type **float** or of a type that can be implicitly converted to **float**.

is the population (known) standard deviation. If zero, the sample standard deviation is used. *@Sigma* is an expression of type **float** or of a type that can be implicitly converted to **float**.

float

· ZTEST is an AGGREGATE function and follows the same conventions as all other AGGREGATE functions in SQL Server.

To calculate the one-tailed probability-value of a z-test for this small dataset, at the hypothesized population mean of 4:

SELECT wct.ZTEST(x,4,0) as ZTEST

FROM (

SELECT 3 UNION ALL

SELECT 6 UNION ALL

SELECT 7 UNION ALL

SELECT 8 UNION ALL

SELECT 6 UNION ALL

SELECT 5 UNION ALL

SELECT 4 UNION ALL

SELECT 2 UNION ALL

SELECT 1 UNION ALL

SELECT 9

) n(x)

This produces the following result

ZTEST

----------------------

0.0905741968513638

(1 row(s) affected)

To calculate the two-tailed probability-value of a z-test for the same dataset, at the hypothesized population mean of 6:

SELECT

CASE

WHEN wct.ZTEST(x,6,0)> .5 THEN 1-wct.ZTEST(x,6,0)

ELSE wct.ZTEST(x,6,0)

END * 2 as ZTEST

FROM (

SELECT 3 UNION ALL

SELECT 6 UNION ALL

SELECT 7 UNION ALL

SELECT 8 UNION ALL

SELECT 6 UNION ALL

SELECT 5 UNION ALL

SELECT 4 UNION ALL

SELECT 2 UNION ALL

SELECT 1 UNION ALL

SELECT 9

) n(x)

This produces the following result

ZTEST

----------------------

0.27391322174094

(1 row(s) affected)