TTEST_q

Updated 2023-10-26 18:54:20.920000

Syntax

SELECT [westclintech].[wct].[TTEST_q] (
   <@Sample1_RangeQuery, nvarchar(4000),>
  ,<@Sample2_RangeQuery, nvarchar(4000),>
  ,<@Tails, int,>
  ,<@Ttype, int,>)

Description

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.

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 Type

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.

{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"0.0134150262766378"}]}

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.

{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"0.0268300525532757"}]}

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.

{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"0.00376520013708237"}]}

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.

{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"0.00753040027416474"}]}

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

{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"0.0078927428250956"}]}

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.

{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"0.0157854856501912"}]}