SQL Server TTEST_PAIRED Function

Updated 2023-10-26 18:38:10.363000

Description

Use the aggregate function TTEST_PAIRED to calculate a two-sample, paired t -test. Use a paired t -test when you have two related observations (i.e., two observations per subject). The TTEST_PAIRED can return any of the following values.

Syntax

SELECT [westclintech].[wct].[TTEST_PAIRED] (
  <@X1, float,> 
 ,<@X2, float,> 
 ,<@Statistic, nvarchar(4000),>)

Arguments

@X1

the x-values for sample number 1. @X1 is an expression of type float or of a type that can be implicitly converted to float.

@X2

the x-values for sample number 2. @X2 is an expression of type float or of a type that can be implicitly converted to float.

@Statistic

identifies the statistic to be returned.

Return Type

float

Remarks

If there is only one row of paired data a NULL will be returned.

@Statistic must be invariant within a GROUP.

If either @X1 IS NULL or @X2 IS NULL, the row is not included in the aggregate.

TTEST_PAIRED is an aggregate function and follows the same conventions as all other aggregate function in SQL Server.

For independent samples with equal variances use TTEST_INDEP.

For independent samples with unequal variances use TTEST_INDEPU.

Effect Size (ES) is calculated as Cohen's d.

N is the number of rows where @X1 IS NOT NULL and @X2 IS NOT NULL.

LCL and UCL are calculated with alpha = .05.

Examples

In this example we calculate the t -statistic for 25 pairs of observations about body weight before and after a diet.

SELECT wct.TTEST_PAIRED(before, after, 'T') as t_observed
FROM
(
    VALUES
        (1, 'M', 218, 196),
        (2, 'F', 126, 139),
        (3, 'M', 209, 188),
        (4, 'F', 140, 140),
        (5, 'M', 192, 173),
        (6, 'F', 152, 137),
        (7, 'F', 112, 123),
        (8, 'M', 226, 203),
        (9, 'M', 201, 181),
        (10, 'M', 193, 174),
        (11, 'F', 124, 136),
        (12, 'F', 133, 141),
        (13, 'M', 204, 184),
        (14, 'M', 212, 191),
        (15, 'F', 144, 134),
        (16, 'M', 182, 164),
        (17, 'M', 200, 180),
        (18, 'M', 189, 170),
        (19, 'F', 156, 140),
        (20, 'M', 203, 183),
        (21, 'M', 207, 186),
        (22, 'M', 211, 190),
        (23, 'F', 122, 124),
        (24, 'M', 202, 182),
        (25, 'F', 134, 147)
) n (id, s, before, after);

This produces the following result.

If we wanted to calculate the one-sided p-value, we would just P1 instead of T.

SELECT wct.TTEST_PAIRED(before, after, 'P1') as one_tailed_p_value
FROM
(
    VALUES
        (1, 'M', 218, 196),
        (2, 'F', 126, 139),
        (3, 'M', 209, 188),
        (4, 'F', 140, 140),
        (5, 'M', 192, 173),
        (6, 'F', 152, 137),
        (7, 'F', 112, 123),
        (8, 'M', 226, 203),
        (9, 'M', 201, 181),
        (10, 'M', 193, 174),
        (11, 'F', 124, 136),
        (12, 'F', 133, 141),
        (13, 'M', 204, 184),
        (14, 'M', 212, 191),
        (15, 'F', 144, 134),
        (16, 'M', 182, 164),
        (17, 'M', 200, 180),
        (18, 'M', 189, 170),
        (19, 'F', 156, 140),
        (20, 'M', 203, 183),
        (21, 'M', 207, 186),
        (22, 'M', 211, 190),
        (23, 'F', 122, 124),
        (24, 'M', 202, 182),
        (25, 'F', 134, 147)
) n (id, s, before, after);

This produces the following result.

Here we show all the values the can be returned by TTEST_PAIRED.

SELECT t.s,
       t.descr,
       wct.TTEST_PAIRED(before, after, t.s) as [Value]
FROM
(
    VALUES
        (1, 'M', 218, 196),
        (2, 'F', 126, 139),
        (3, 'M', 209, 188),
        (4, 'F', 140, 140),
        (5, 'M', 192, 173),
        (6, 'F', 152, 137),
        (7, 'F', 112, 123),
        (8, 'M', 226, 203),
        (9, 'M', 201, 181),
        (10, 'M', 193, 174),
        (11, 'F', 124, 136),
        (12, 'F', 133, 141),
        (13, 'M', 204, 184),
        (14, 'M', 212, 191),
        (15, 'F', 144, 134),
        (16, 'M', 182, 164),
        (17, 'M', 200, 180),
        (18, 'M', 189, 170),
        (19, 'F', 156, 140),
        (20, 'M', 203, 183),
        (21, 'M', 207, 186),
        (22, 'M', 211, 190),
        (23, 'F', 122, 124),
        (24, 'M', 202, 182),
        (25, 'F', 134, 147)
) n (id, s, before, after)
    CROSS APPLY
(
    VALUES
        ('P1', 'one_tailed_p_value'),
        ('P2', 'two_tailed_p_value'),
        ('T', 't_observed'),
        ('ES', 'effect_size'),
        ('N', 'num_observed'),
        ('DF', 'deg_freedom'),
        ('MD', 'mean_difference'),
        ('SDD', 'std_dev_diff'),
        ('SE', 'std_err_mean'),
        ('LCL', 'lower_ci'),
        ('UCL', 'upper_ci'),
        ('R', 'correlation')
) t (s, descr)
GROUP BY t.s,
         t.descr;

This produces the following result.

Since TTEST_PAIRED is an aggregate function, we could have modified our SQL to produce a result for each value of s in the derived table, n.

SELECT *
FROM
(
    SELECT n.s as gender,
           t.s as statistic,
           t.descr,
           wct.TTEST_PAIRED(before, after, t.s) as [Value]
    FROM
    (
        VALUES
            (1, 'M', 218, 196),
            (2, 'F', 126, 139),
            (3, 'M', 209, 188),
            (4, 'F', 140, 140),
            (5, 'M', 192, 173),
            (6, 'F', 152, 137),
            (7, 'F', 112, 123),
            (8, 'M', 226, 203),
            (9, 'M', 201, 181),
            (10, 'M', 193, 174),
            (11, 'F', 124, 136),
            (12, 'F', 133, 141),
            (13, 'M', 204, 184),
            (14, 'M', 212, 191),
            (15, 'F', 144, 134),
            (16, 'M', 182, 164),
            (17, 'M', 200, 180),
            (18, 'M', 189, 170),
            (19, 'F', 156, 140),
            (20, 'M', 203, 183),
            (21, 'M', 207, 186),
            (22, 'M', 211, 190),
            (23, 'F', 122, 124),
            (24, 'M', 202, 182),
            (25, 'F', 134, 147)
    ) n (id, s, before, after)
        CROSS APPLY
    (
        VALUES
            ('P1', 'one_tailed_p_value'),
            ('P2', 'two_tailed_p_value'),
            ('T', 't_observed'),
            ('ES', 'effect_size'),
            ('N', 'num_observed'),
            ('DF', 'deg_freedom'),
            ('MD', 'mean_difference'),
            ('SDD', 'std_dev_diff'),
            ('SE', 'std_err_mean'),
            ('LCL', 'lower_ci'),
            ('UCL', 'upper_ci'),
            ('R', 'correlation')
    ) t (s, descr)
    GROUP BY n.s,
             t.s,
             t.descr
) p
PIVOT
(
    MIN([Value])
    FOR gender in ([M], [F])
) as d
ORDER BY 1;

This produces the following result.