TTEST_INDEP
Updated 2023-10-26 18:15:13.207000
Syntax
SELECT [westclintech].[wct].[TTEST_INDEP] (
<@Label, sql_variant,>
,<@Value, float,>
,<@Statistic, nvarchar(4000),>
,<@XLabel, sql_variant,>)
Description
Use the aggregate function TTEST_INDEP to perform a t -test on data in two samples, x and y assuming equal variance. An independent samples t-test is used when you want to compare the means of a normally distributed interval dependent variable for two independent groups.
The TTEST_INDEP can return any of the following values.
{
"columns": [
{
"field": "Statistic",
"maxWidth": 100
},
{
"field": "Description",
"width": 225
}
],
"rows": [
{
"Statistic": "P1",
"Description": "one-tailed probability"
},
{
"Statistic": "P2",
"Description": "two-tailed probability"
},
{
"Statistic": "T",
"Description": "t-statistic"
},
{
"Statistic": "DF",
"Description": "degrees of freedom"
},
{
"Statistic": "N1",
"Description": "number of observations (in the x group)"
},
{
"Statistic": "N2",
"Description": "number of observations (in the y group)"
},
{
"Statistic": "ES",
"Description": "effect size"
},
{
"Statistic": "SD",
"Description": "pooled variance"
},
{
"Statistic": "MD",
"Description": "mean difference"
},
{
"Statistic": "SE",
"Description": "standard error"
},
{
"Statistic": "LCL",
"Description": "lower confidence level"
},
{
"Statistic": "UCL",
"Description": "upper confidence level"
}
]
}
Arguments
@Statistic
identifies the statistic to be returned.
@Value
the x-values and y-values to be used in the computation. @Value is an expression of type float or of a type that can be implicitly converted to float.
@XLabel
Identifies which of the two values in the @Label column will be treated as x (or sample 1).
@Label
the column variable which differentiates the two samples. You can use @Label to identify the control group and the treatment group.
Return Type
float
Remarks
@Statistic must be invariant within a GROUP.
There must be exactly 2 values for @Label within a GROUP.
@XLabel must be invariant within a GROUP.
TTEST_INDEP is an aggregate function and follows the same conventions as all other aggregate function in SQL Server.
For paired t-test use TTEST_PAIRED.
For independent samples with unequal variances use TTEST_INDEPU.
If @Value IS NULL, then it is not included in the aggregate
Effect Size (ES) is calculated as Cohen’s d.
LCL and UCL are calculated with alpha = .05
Examples
Here’s a small sample of test scores broken out by gender.
SELECT wct.TTEST_INDEP(x, val, 'P1', 'F') as one_sided_p_value
FROM
(
VALUES
(1, 'M', 420),
(2, 'M', 647),
(3, 'M', 629),
(4, 'M', 569),
(5, 'M', 426),
(6, 'F', 554),
(7, 'F', 502),
(8, 'F', 580),
(9, 'M', 626),
(10, 'F', 412),
(11, 'F', 480),
(12, 'M', 478),
(13, 'F', 566),
(14, 'F', 491),
(15, 'M', 511),
(16, 'F', 556),
(17, 'F', 359),
(18, 'F', 451),
(19, 'F', 430),
(20, 'M', 441),
(21, 'M', 490),
(22, 'M', 578),
(23, 'F', 385),
(24, 'M', 592),
(25, 'M', 333)
) n (id, x, val);
This produces the following result.
{"columns":[{"field":"one_sided_p_value","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"one_sided_p_value":"0.143336729580001"}]}
If we wanted to calculate the t-statistic, we would just use T instead of P1.
SELECT wct.TTEST_INDEP(x, val, 'T', 'F') as t_observed
FROM
(
VALUES
(1, 'M', 420),
(2, 'M', 647),
(3, 'M', 629),
(4, 'M', 569),
(5, 'M', 426),
(6, 'F', 554),
(7, 'F', 502),
(8, 'F', 580),
(9, 'M', 626),
(10, 'F', 412),
(11, 'F', 480),
(12, 'M', 478),
(13, 'F', 566),
(14, 'F', 491),
(15, 'M', 511),
(16, 'F', 556),
(17, 'F', 359),
(18, 'F', 451),
(19, 'F', 430),
(20, 'M', 441),
(21, 'M', 490),
(22, 'M', 578),
(23, 'F', 385),
(24, 'M', 592),
(25, 'M', 333)
) n (id, x, val);
This produces the following result.
{"columns":[{"field":"t_observed","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"t_observed":"-1.09076167249426"}]}
Here we show all the values the can be returned by TTEST_INDEP.
SELECT t.s,
t.descr,
wct.TTEST_INDEP(x, val, t.s, 'F') as Value
FROM
(
VALUES
(1, 'M', 420),
(2, 'M', 647),
(3, 'M', 629),
(4, 'M', 569),
(5, 'M', 426),
(6, 'F', 554),
(7, 'F', 502),
(8, 'F', 580),
(9, 'M', 626),
(10, 'F', 412),
(11, 'F', 480),
(12, 'M', 478),
(13, 'F', 566),
(14, 'F', 491),
(15, 'M', 511),
(16, 'F', 556),
(17, 'F', 359),
(18, 'F', 451),
(19, 'F', 430),
(20, 'M', 441),
(21, 'M', 490),
(22, 'M', 578),
(23, 'F', 385),
(24, 'M', 592),
(25, 'M', 333)
) n (id, x, val)
CROSS APPLY
(
VALUES
('P1', 'one_tailed_p_value'),
('P2', 'two_tailed_p_value'),
('T', 't_observed'),
('ES', 'effect_size'),
('N1', 'num_observed_one'),
('N2', 'num_observed_two'),
('SD', 'pooled_variance'),
('DF', 'deg_freedom'),
('MD', 'mean_difference'),
('SE', 'std_error'),
('LCL', 'lower_ci'),
('UCL', 'upper_ci')
) t (s, descr)
GROUP BY t.s,
t.descr;
This produces the following result.
{"columns":[{"field":"s"},{"field":"descr"},{"field":"Value","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"s":"P1","descr":"one_tailed_p_value","Value":"0.143336729580001"},{"s":"P2","descr":"two_tailed_p_value","Value":"0.286673459160002"},{"s":"T","descr":"t_observed","Value":"-1.09076167249426"},{"s":"ES","descr":"effect_size","Value":"-0.436654132144636"},{"s":"N1","descr":"num_observed_one","Value":"12"},{"s":"N2","descr":"num_observed_two","Value":"13"},{"s":"SD","descr":"pooled_variance","Value":"7558.09698996656"},{"s":"DF","descr":"deg_freedom","Value":"23"},{"s":"MD","descr":"mean_difference","Value":"-37.9615384615385"},{"s":"SE","descr":"std_error","Value":"34.8027799461741"},{"s":"LCL","descr":"lower_ci","Value":"-109.956574060931"},{"s":"UCL","descr":"upper_ci","Value":"34.0334971378542"}]}