Login     Register

        Contact Us     Search

XLeratorDB/statistics Documentation

SQL Server T.TEST function



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)


Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service