/*

The following example is how you can do a F-test. Once again,

it is best if you have your numbers in a column and identify

them with a Group ID. That way you have the ability to run as

many sets as you would like. Change things around and see the

different result sets you can get.

The following reference is a clear explanation of the mathematics

and the interpretation of the numbers. The code examples show how

you can calculate the numbers with some programming ideas involved.

By C. Eric Cashon

Reference:

DeMuth J. E.,Basic Statistics and Pharmaceutical Statistical Applications,

Marcel Dekker, NY, 1999, P.207-210.

*/

USE tempdb

CREATE TABLE fTest(TestID INTEGER NOT NULL IDENTITY PRIMARY KEY,

GroupID1 INTEGER, TestSample1 REAL)

INSERT INTO fTest(GroupID1, TestSample1) VALUES (1, 277.3)

INSERT INTO fTest(GroupID1, TestSample1) VALUES (1, 280.3)

INSERT INTO fTest(GroupID1, TestSample1) VALUES (1, 279.1)

INSERT INTO fTest(GroupID1, TestSample1) VALUES (1, 275.2)

INSERT INTO fTest(GroupID1, TestSample1) VALUES (1, 273.6)

INSERT INTO fTest(GroupID1, TestSample1) VALUES (1, 276.7)

INSERT INTO fTest(GroupID1, TestSample1) VALUES (1, 281.7)

INSERT INTO fTest(GroupID1, TestSample1) VALUES (1, 278.7)

INSERT INTO fTest(GroupID1, TestSample1) VALUES (1, 278.4)

INSERT INTO fTest(GroupID1, TestSample1) VALUES (1, 272.9)

INSERT INTO fTest(GroupID1, TestSample1) VALUES (1, 274.7)

INSERT INTO fTest(GroupID1, TestSample1) VALUES (1, 276.8)

INSERT INTO fTest(GroupID1, TestSample1) VALUES (1, 269.1)

INSERT INTO fTest(GroupID1, TestSample1) VALUES (1, 276.3)

INSERT INTO fTest(GroupID1, TestSample1) VALUES (1, 273.1)

INSERT INTO fTest(GroupID1, TestSample1) VALUES (2, 271.6)

INSERT INTO fTest(GroupID1, TestSample1) VALUES (2, 274.8)

INSERT INTO fTest(GroupID1, TestSample1) VALUES (2, 271.2)

INSERT INTO fTest(GroupID1, TestSample1) VALUES (2, 277.6)

INSERT INTO fTest(GroupID1, TestSample1) VALUES (2, 274.5)

INSERT INTO fTest(GroupID1, TestSample1) VALUES (2, 275.7)

INSERT INTO fTest(GroupID1, TestSample1) VALUES (2, 276.1)

INSERT INTO fTest(GroupID1, TestSample1) VALUES (2, 275.9)

INSERT INTO fTest(GroupID1, TestSample1) VALUES (2, 275.5)

INSERT INTO fTest(GroupID1, TestSample1) VALUES (2, 274.0)

INSERT INTO fTest(GroupID1, TestSample1) VALUES (2, 274.9)

INSERT INTO fTest(GroupID1, TestSample1) VALUES (2, 269.2)

INSERT INTO fTest(GroupID1, TestSample1) VALUES (2, 283.2)

INSERT INTO fTest(GroupID1, TestSample1) VALUES (2, 280.6)

INSERT INTO fTest(GroupID1, TestSample1) VALUES (2, 274.6)

INSERT INTO fTest(GroupID1, TestSample1) VALUES (3, 275.5)

INSERT INTO fTest(GroupID1, TestSample1) VALUES (3, 274.2)

INSERT INTO fTest(GroupID1, TestSample1) VALUES (3, 267.5)

INSERT INTO fTest(GroupID1, TestSample1) VALUES (3, 274.2)

INSERT INTO fTest(GroupID1, TestSample1) VALUES (3, 270.5)

INSERT INTO fTest(GroupID1, TestSample1) VALUES (3, 284.4)

INSERT INTO fTest(GroupID1, TestSample1) VALUES (3, 275.6)

INSERT INTO fTest(GroupID1, TestSample1) VALUES (3, 277.1)

INSERT INTO fTest(GroupID1, TestSample1) VALUES (3, 272.3)

INSERT INTO fTest(GroupID1, TestSample1) VALUES (3, 273.4)

INSERT INTO fTest(GroupID1, TestSample1) VALUES (3, 275.1)

INSERT INTO fTest(GroupID1, TestSample1) VALUES (3, 273.7)

INSERT INTO fTest(GroupID1, TestSample1) VALUES (3, 268.7)

INSERT INTO fTest(GroupID1, TestSample1) VALUES (3, 275.0)

INSERT INTO fTest(GroupID1, TestSample1) VALUES (3, 268.3)

--Take a look at the table

SELECT * FROM fTest

--Find the Standard Deviation and Mean

SELECT STDEV(TestSample1) AS SD, AVG(TestSample1) AS Mean

FROM fTest

GROUP BY GroupID1

--Find MSw(mean squared within)

SELECT 14*(SUM(T1.SD)/42) AS MSw FROM

(SELECT (SQUARE(STDEV(TestSample1))) AS SD

FROM fTest

GROUP BY GroupID1) T1

--Find Xg(pooled mean)

SELECT 15*(SUM(T1.Mean)/45) AS Xg FROM

(SELECT (AVG(TestSample1)) AS Mean

FROM fTest

GROUP BY GroupID1) T1

--Find MSb(mean squared between)

SELECT (15 * (SUM(SQUARE(Mean - Xg))))/ 2 AS MSb FROM

(SELECT 1 AS JoinID,(AVG(TestSample1)) AS Mean

FROM fTest

GROUP BY GroupID1) AS T1 INNER JOIN

(SELECT 1 AS JoinID, 15*(SUM(T2.Mean)/45) AS Xg

FROM

(SELECT (AVG(TestSample1)) AS Mean

FROM fTest

GROUP BY GroupID1)T2) AS T3

ON T1.JoinID = T3.JoinID

--Put it all together and let the database system return the result.

--With a little more work you could even F-test subgroups down a

--column.

--alpha = .05, reject Ho if F > F(2,42)(.95) = 3.23

SELECT T6.MSw, T6.Xg, T6.MSb, (T6.MSb/T6.MSw) AS Ftest FROM

(SELECT DISTINCT (SELECT 14 * (SUM(T1.SD)/42) FROM

(SELECT (SQUARE(STDEV(TestSample1))) AS SD

FROM fTest

GROUP BY GroupID1) T1) AS MSw,

(SELECT 15 * (SUM(T2.Mean)/ 45)

FROM

(SELECT (AVG(TestSample1)) AS Mean

FROM fTest

GROUP BY GroupID1)T2) AS Xg,

(SELECT (15 * (SUM(SQUARE(Mean - Xg))))/ 2 FROM

(SELECT 1 AS JoinID,(AVG(TestSample1)) AS Mean

FROM fTest

GROUP BY GroupID1) AS T3 INNER JOIN

(SELECT 1 AS JoinID, 15 * (SUM(T4.Mean)/ 45) AS Xg

FROM

(SELECT (AVG(TestSample1)) AS Mean

FROM fTest

GROUP BY GroupID1)T4) AS T5

ON T3.JoinID = T5.JoinID) AS MSb

FROM fTest) T6

--Clean up

DROP TABLE fTest