ZTEST
Updated: 30 September 2010
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)