ZTEST
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.Syntax
SELECT [wctStatistics].[wct].[ZTEST] (
<@Known_x, float,>
,<@mu, nvarchar(4000),>
,<@Sigma, float,>)
Arguments
@Known_x
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.
@mu
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.
@Sigma
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.
Return Types
float
Remarks
· ZTEST is an AGGREGATE function and follows the same conventions as all other AGGREGATE functions in SQL Server.
Examples
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)