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
.jpg)
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