TTEST_INDEPU
Updated 2023-10-26 18:26:56.167000
Syntax
SELECT [westclintech].[wct].[TTEST_INDEPU] (
<@Label, sql_variant,>
,<@Value, float,>
,<@Statistic, nvarchar(4000),>
,<@XLabel, sql_variant,>)
Description
Use the aggregate function TTEST_INDEPU to perform a t -test on data in two samples, x and y assuming unequal 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_INDEPU can return any of the following values.
{
"columns": [
{
"field": "Statistic",
"width": 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 if 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.
If @Value IS NULL it is not included in the aggregate.
TTEST_INDEPU 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 equal variances use TTEST_INDEP.
Effect Size (ES) is calculated as Cohen’s d.
Unlike EXCEL and some other statistics packages, DF is not rounded before calculating the one-tailed or two-tailed p-values.
LCL and UCL are calculated with alpha = .05
Examples
Here’s a small sample of test scores broken out by gender.
SELECT wct.TTEST_INDEPU(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.140974751777123"}]}
If we wanted to calculate the t-statistic, we would just use T instead of P1
SELECT wct.TTEST_INDEPU(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.10263350525694"}]}
Here we show all the values that can be returned by TTEST_INDEPU.
SELECT t.s,
t.descr,
wct.TTEST_INDEPU(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'),
('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.140974751777123"},{"s":"P2","descr":"two_tailed_p_value","Value":"0.281949503554246"},{"s":"T","descr":"t_observed","Value":"-1.10263350525694"},{"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":"DF","descr":"deg_freedom","Value":"22.2901656180061"},{"s":"MD","descr":"mean_difference","Value":"-37.9615384615385"},{"s":"SE","descr":"std_error","Value":"34.428065427499"},{"s":"LCL","descr":"lower_ci","Value":"-109.307125545989"},{"s":"UCL","descr":"upper_ci","Value":"33.3840486229124"}]}