Login     Register

        Contact Us     Search

XLeratorDB/statistics Documentation

SQL Server paired T test


TTEST_PAIRED

Updated: 17 July 2012


Note: This documentation is for the SQL2008 (and later) version of this XLeratorDB function, it is not compatible with SQL Server 2005.
Click here for the SQL2005 version of the TTEST_PAIRED function


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.
 
P1           -              one-tailed probability
P2           -              two-tailed probability
T             -              t-statistic
DF           -              degrees of freedom
N             -              number of observations
ES           -              effect size
SE           -              standard error
R             -              Pearson’s correlation coefficient
LCL         -              lower confidence level
UCL        -              upper confidence level
 
Syntax
XLeratorDB syntax for SQL Server analytic function TTEST_PAIRED
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 Types
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.
            t_observed
----------------------
      4.34248118673448
 
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.
    one_tailed_p_value
----------------------
 0.000110546467918379
 
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.
s    descr                               Value
---- ------------------ ----------------------
P1   one_tailed_p_value   0.000110546467918379
P2   two_tailed_p_value   0.000221092935836758
T    t_observed               4.34248118673448
ES   effect_size             0.868496237346895
N    num_observed                           25
DF   deg_freedom                            24
MD   mean_difference                     11.44
SDD  std_dev_diff             13.1721929330946
SE   std_err_mean             2.63443858661892
LCL  lower_ci                 6.00278599037985
UCL  upper_ci                 16.8772140096201
R    correlation              0.97692313682967
 
Since TTEST_PAIRED is an aggegate 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.
statistic descr                                   M                      F
--------- ------------------ ---------------------- ----------------------
DF        deg_freedom                            14                      9
ES        effect_size              15.8348061330513     -0.154626901015141
LCL       lower_ci                 19.5578922229037      -10.1274153610638
MD        mean_difference          20.2666666666667      -1.79999999999998
N         num_observed                           15                     10
P1        one_tailed_p_value   1.01201526614611E-18      0.318277706057421
P2        two_tailed_p_value   2.02403053229221E-18      0.636555412114841
R         correlation             0.999804279842337      0.545476196975635
SDD       std_dev_diff             1.27988094684437       11.6409239801277
SE        std_err_mean            0.330463839483762       3.68118338460761
T         t_observed               61.3279404437097     -0.488973194741248
UCL       upper_ci                 20.9754411104297       6.52741536106385
 


Copyright 2008-2024 Westclintech LLC         Privacy Policy        Terms of Service