Updated: 6 August 2010
Use TTEST_q to calculate the probability associated with Student’s t-test. Use TTEST_q to determine whether two samples are likely to have come from the same two underlying populations that have the same mean.
Syntax
SELECT [wctStatistics].[wct].[TTEST_q] (
<@Sample1_RangeQuery, nvarchar(4000),>
,<@Sample2_RangeQuery, nvarchar(4000),>
,<@Tails, int,>
,<@Ttype, int,>)
Arguments
@Sample1_RangeQuery
the select statement, as text, used to determine the first set of values to be used in the TTEST_q calculation.
@Sample2_RangeQuery
the select statement, as text, used to determine the second set of values to be used in the TTEST_q calculation.
@Tails
specifies the number of distribution tails. If tails = 1, TTEST uses the one-tailed distribution. If tails = 2, TTEST uses the two-tailed distribution.
@Ttype
is the kind of t-Test to perform. If @Ttype =1, perform the paired test. If @Ttype = 2, perform the two-sample equal variance test. If @Ttype = 3, perform the two-sample unequal variance test.
Return Types
float
Remarks
· If the number of rows in dataset 1 or dataset 2 have a different number of data points and @Ttype = 1, TTEST returns an error.
· If @Tails is any value other than 1 or 2, TTEST returns an error.
· If @Ttype is any value other than 1, 2 or 3, TTEST returns an error.
· No GROUP BY is required for this function even though it produces aggregated results.
Examples
This script creates two temporary tables, #data1 and #data2, with same number of rows in each.
CREATE TABLE #data1(
[num] [float] NOT NULL
)
INSERT INTO #data1 VALUES (154.3)
INSERT INTO #data1 VALUES (191)
INSERT INTO #data1 VALUES (163.4)
INSERT INTO #data1 VALUES (168.6)
INSERT INTO #data1 VALUES (187)
INSERT INTO #data1 VALUES (200.4)
INSERT INTO #data1 VALUES (162.5)
CREATE TABLE #data2(
[num] [float] NOT NULL
)
INSERT INTO #data2 VALUES (230.4)
INSERT INTO #data2 VALUES (202.8)
INSERT INTO #data2 VALUES (202.8)
INSERT INTO #data2 VALUES (216.8)
INSERT INTO #data2 VALUES (192.9)
INSERT INTO #data2 VALUES (194.4)
INSERT INTO #data2 VALUES (211.7)
To calculate the probability associated with a Student's paired t-Test, with a one-tailed distribution we would enter the following:
select wct.TTEST_q('Select num from #data1', 'Select num from #data2',1,1)
This produces the following result
----------------------
0.0134150262766378
(1 row(s) affected)
To calculate the probability associated with a Student's paired t-Test, with a two-tailed distribution we would enter the following:
select wct.TTEST_q('Select num from #data1', 'Select num from #data2',2,1)
This produces the following result
----------------------
0.0268300525532757
(1 row(s) affected)
This script creates two temporary tables, #data1 and #data2, with #data2 having one more row than #data1.
CREATE TABLE #data1(
[num] [float] NOT NULL
)
INSERT INTO #data1 VALUES (165.9)
INSERT INTO #data1 VALUES (210.3)
INSERT INTO #data1 VALUES (166.8)
INSERT INTO #data1 VALUES (182.3)
INSERT INTO #data1 VALUES (182.1)
INSERT INTO #data1 VALUES (218)
INSERT INTO #data1 VALUES (170.1)
CREATE TABLE #data2(
[num] [float] NOT NULL
)
INSERT INTO #data2 VALUES (212.1)
INSERT INTO #data2 VALUES (203.5)
INSERT INTO #data2 VALUES (210.3)
INSERT INTO #data2 VALUES (228.4)
INSERT INTO #data2 VALUES (206.2)
INSERT INTO #data2 VALUES (203.2)
INSERT INTO #data2 VALUES (224.9)
INSERT INTO #data2 VALUES (202.6)
To calculate the probability associated with a Student's two sample equal variance t-Test, with a one-tailed distribution:
select wct.TTEST_q('Select num from #data1', 'Select num from #data2',1,2)
This produces the following result
----------------------
0.00376520013708237
(1 row(s) affected)
To calculate the probability associated with a Student's two sample equal variance t-Test, with a two-tailed distribution:
select wct.TTEST_q('Select num from #data1', 'Select num from #data2',2,2)
This produces the following result
----------------------
0.00753040027416474
(1 row(s) affected)
To calculate the probability associated with a Student's two sample unequal variance t-Test, with a one-tailed distribution:
select wct.TTEST_q('Select num from #data1', 'Select num from #data2',1,3)
This produces the following result
----------------------
0.0078927428250956
(1 row(s) affected)
To calculate the probability associated with a Student's two sample unequal variance t-Test, with a two-tailed distribution:
select wct.TTEST_q('Select num from #data1', 'Select num from #data2',2,3)
This produces the following result
----------------------
0.0157854856501912
(1 row(s) affected)