10 Replies Latest reply on Mar 2, 2013 10:43 PM by Frank Kulash

# Students T Critical Value

Hi All,

I'm trying to calculate 90/95% confidence intervals on data that I have. Oracle doesn't seem to be able to give me the Critical T-Values from the Student's T Table given N-1 degrees of freedom and a given confidence level, ie 90 or 95%. I've managed to create the student's t table in Oracle, I've named the table STUDENTS_T_TABLE_REF. Below is a small sample of the table:
``````DEGREE_FREEDOM     CONFIDENCE_LVL     CRITICAL_T_VALUE
1     90     6.3138
2     90     2.92
3     90     2.3534
4     90     2.1319
5     90     2.015
6     90     1.9432
7     90     1.8946
8     90     1.8595
9     90     1.8331
10     90     1.8124
11     90     1.7959
12     90     1.7823
13     90     1.7709
14     90     1.7613
1     95     12.7065
2     95     4.3026
3     95     3.1824
4     95     2.7764
5     95     2.5706
6     95     2.4469
7     95     2.3646
8     95     2.306
9     95     2.2621
10     95     2.2282
11     95     2.201
12     95     2.1788
13     95     2.1604
14     95     2.1448``````
I want to create a function or procedure that looks up the Critical T Value from this table given a user inputted confidence level, ie 95%, given N-1 degrees of freedom where N = Sample size. The sample sizes will be pulled from aggregate tables I create. So for instance I have a table called AGGREGATE_DATA_SAMPLES and within this table I have a column called "SAMPLE_SIZE". What I would like to have is an oracle function or procedure that allows the user to specify the following as input parameters:

1. Name of the column in the aggregate table that contains the sample sizes. The sample size inputted is substracted by 1 to get degrees of freedom.
2. Confidence Level, ie 95.

This function/procedure in turn outputs or returns the Critical T Value from the STUDENTS_T_TABLE_REF table. So for example I'll name of the function/procedure CRITICAL_T and call it by inputting the parameters above so it would look like, CRITICAL_T(sample_size,95) and it would return, for every row in my AGGREGATE_DATA_SAMPLES table the critical t value. So if I had 3 rows in my AGGREGATE_DATA_SAMPLES table, it would return the critical t given the sample size and confidence level of 95%.
`````` Select sample_size, critical_t(sample_size,95) from AGGREGATE_DATA_SAMPLES;

SAMPLE_SIZE   CRITICAL_T_VALUE (computed)
9                  2.306
14                 2.1604
4                  3.1824``````
Hope this makes sense. I'm using Oracle 10g version. Any help would be appreciated.

Thanks,
Ed
• ###### 1. Re: Students T Critical Value
Hi,
if I understand you correctly, you don't need to compute anything, just look it up, and you can do that with a join:
``````select ds.sample_size,
sr.critical_t_value
from aggregate_data_samples ds,
students_t_table_ref sr
where sr.confidence_level = 95
and sr.degree_freedom = ds.sample_size-1;``````
//Tine
• ###### 2. Re: Students T Critical Value
Hi Christine,

Thanks for the response. Yes, I can do this as a join but I would like it setup as a function or procedure if possible.

Ed
• ###### 3. Re: Students T Critical Value
Hi, Ed,
spalato76 wrote:
Hi All,

I'm trying to calculate 90/95% confidence intervals on data that I have. Oracle doesn't seem to be able to give me the Critical T-Values from the Student's T Table given N-1 degrees of freedom and a given confidence level, ie 90 or 95%.
Are you sure none of the built-in statistical functions do what you you want? If one of them is anywehere close to what you want, it would probably be easier to build on that rather than start from scratch.
I've managed to create the student's t table in Oracle, I've named the table STUDENTS_T_TABLE_REF. Below is a small sample of the table:
``````DEGREE_FREEDOM     CONFIDENCE_LVL     CRITICAL_T_VALUE
1     90     6.3138
2     90     2.92
...
14     95     2.1448``````
Post CREATE TABLE and INSERT statements. You don't have to post INSERT statements for the entire table, just enough to test the function.
>
>
I want to create a function or procedure that looks up the Critical T Value from this table given a user inputted confidence level, ie 95%, given N-1 degrees of freedom where N = Sample size. The sample sizes will be pulled from aggregate tables I create. So for instance I have a table called AGGREGATE_DATA_SAMPLES and within this table I have a column called "SAMPLE_SIZE". What I would like to have is an oracle function or procedure that allows the user to specify the following as input parameters:

1. Name of the column Do you really want to pass the name of the column? That would require dynamic SQL. Can't you derive what values you need from the column (using built-in functions like COUNT) first, and then just call the function with those values as arguments? It will make the function much simpler and more robust.
in the aggregate table that contains the sample sizes. The sample size inputted is substracted by 1 to get degrees of freedom.
2. Confidence Level, ie 95.

This function/procedure in turn outputs or returns the Critical T Value from the STUDENTS_T_TABLE_REF table. So for example I'll name of the function/procedure CRITICAL_T and call it by inputting the parameters above so it would look like, CRITICAL_T(sample_size,95) and it would return, for every row in my AGGREGATE_DATA_SAMPLES table the critical t value. So if I had 3 rows in my AGGREGATE_DATA_SAMPLES table, it would return the critical t given the sample size and confidence level of 95%.
`````` Select sample_size, critical_t(sample_size,95) from AGGREGATE_DATA_SAMPLES;

SAMPLE_SIZE   CRITICAL_T_VALUE (computed)
9                  2.306
14                 2.1604
4                  3.1824``````
Hope this makes sense. I'm using Oracle 10g version. Any help would be appreciated.
It would help a lot if you posted CREATE TABLE and INSERT statements for both aggregate_data_samples and students_t_table_ref (again, just enough to calculate the results you posted from the query above). Explain, step by step, how you get the results you want from those tables. Not all of the people who want to help you know a lot about statistics.
• ###### 4. Re: Students T Critical Value
Hi Ed,

why? ;) If you use it in a select like the one you posted, the only thing you'll gain is a loss of performance.

But it is possible - now you have the select, you can easily create a function as a wrapper around it which takes said in parameters and returns that which gets selected (has to be a function, not a procedure, since procedures cannot be called from sql). Give it a try ;)

//Tine
• ###### 5. Re: Students T Critical Value
Yes, I can do this as a join
Any specific reason for this?
Why do you want to complicate things (by creating an extra object - proc/func) when it can be done with a simple query
but I would like it setup as a function or procedure if possible.
Did you try doing this?
I guess shouldn't be a big deal... Could you please show your efforts?
• ###### 6. Re: Students T Critical Value
Hi Frank,

Thanks for the reply. Sorry for not creating the create and insert statements....I figured it would not be necessary. As far the built in statistical tests are concerned...the t tests provide you with the observed T value but not the critical t which is needed to multiple by the standard error to get the confidence interval. I can calculate the standard error, just need to multiply it by the critical t so what I really want is just a simple function to lookup the critical t value given the n-1 degrees of freedom and confidence level. Perhaps I've complicated it too much....all I need is a function that provides the critical t value given the degrees of freedom and confidence level:
``````create table students_t_table_ref (
degrees_freedom   number,
confidence_lvl    number,
critical_t_value  number
);

insert into students_t_table_ref values(1,90,6.3138);
insert into students_t_table_ref values(2,90,2.92);
insert into students_t_table_ref values(3,90,2.3534);
insert into students_t_table_ref values(4,90,2.1319);
insert into students_t_table_ref values(5,90,2.015);
insert into students_t_table_ref values(6,90,1.9432);
insert into students_t_table_ref values(7,90,1.8946);
insert into students_t_table_ref values(8,90,1.8595);
insert into students_t_table_ref values(9,90,1.8331);
insert into students_t_table_ref values(10,90,1.8124);
insert into students_t_table_ref values(11,90,1.7959);
insert into students_t_table_ref values(12,90,1.7823);
insert into students_t_table_ref values(13,90,1.7709);
insert into students_t_table_ref values(14,90,1.7613);
insert into students_t_table_ref values(1,95,12.7065);
insert into students_t_table_ref values(2,95,4.3026);
insert into students_t_table_ref values(3,95,3.1824);
insert into students_t_table_ref values(4,95,2.7764);
insert into students_t_table_ref values(5,95,2.5706);
insert into students_t_table_ref values(6,95,2.4469);
insert into students_t_table_ref values(7,95,2.3646);
insert into students_t_table_ref values(8,95,2.306);
insert into students_t_table_ref values(9,95,2.2621);
insert into students_t_table_ref values(10,95,2.2282);
insert into students_t_table_ref values(11,95,2.201);
insert into students_t_table_ref values(12,95,2.1788);
insert into students_t_table_ref values(13,95,2.1604);
insert into students_t_table_ref values(14,95,2.1448);``````
Ed

Edited by: spalato76 on Mar 2, 2013 12:02 PM
• ###### 7. Re: Students T Critical Value
Not tested
``````create or replace function ctv(ss number,cl number)
return number
as
v_ret number;
begin

select critical_t_value
into v_ret
from students_t_table_ref
where
degrees_freedom = ss
AND confidence_lvl = cl;

return v_ret;

end ctv;``````
• ###### 8. Re: Students T Critical Value
Hi,

So you just want a function that subtracts 1 from one of its arguments, and then looks up something in a table?
Here's a function that does that:
``````CREATE OR REPLACE FUNCTION  critical_t
(   in_sample_size     IN   NUMBER
,   in_confidence_lvl  IN   NUMBER
)
RETURN NUMBER
IS
return_val  NUMBER;
BEGIN
SELECT  MIN (critical_t_value)
INTO    return_val
FROM    students_t_table_ref
WHERE   degrees_freedom  = in_sample_size - 1
AND         confidence_lvl   = in_confidence_lvl;

RETURN  return_val;
END  critical_t;
/
SHOW ERRORS``````
As others have said, it's easy enough to do with a join.
A function is more convenient. There's nothing wrong with using a function for this when speed isn't important.
• ###### 9. Re: Students T Critical Value
Thanks Frank.

If I wanted to extend the concept of the function below....and let the sample size be pulled from a column in another table, how could I do this. The function below is great if you are manually inputting the sample size but if you have an aggregate table with many sample sizes you need a critical t value for...how can I do this? Below is the code to create the aggregate sample sizes table:
``````create table aggregate_sample_sizes (
sample_id number,
sample_size number
);

insert into aggregate_sample_sizes values(1,6);
insert into aggregate_sample_sizes values(2,5);
insert into aggregate_sample_sizes values(3,6);
insert into aggregate_sample_sizes values(4,17);
insert into aggregate_sample_sizes values(5,3);
insert into aggregate_sample_sizes values(6,20);
insert into aggregate_sample_sizes values(7,19);
insert into aggregate_sample_sizes values(8,18);
insert into aggregate_sample_sizes values(9,9);
insert into aggregate_sample_sizes values(10,11);
insert into aggregate_sample_sizes values(11,11);
insert into aggregate_sample_sizes values(12,6);
insert into aggregate_sample_sizes values(13,2);
insert into aggregate_sample_sizes values(14,9);``````
Thanks,
Ed
• ###### 10. Re: Students T Critical Value
Hi,

Thanks for posting the table. Don't forget to post the results you want from that table (and whatever other inputs may be needed, such as confidence_lvl=95), and a step-by-step explanation of how you get those results from that table (and the students_t_table_ref table).