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

@Sample2_RangeQuery

the select statement, as text, used to determine the second set of values to be used in the TTEST_q calculation.

@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.

@Sample1_RangeQuery

the select statement, as text, used to determine the first 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.

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"}]}