Correlation Query

[AJ]

I am looking to compute correlation. I have a table with x values and y
values and would like to run something similar to the CORREL function in
EXCEL.
Does anyone have a query like that which works?

 

[James A. Fortune]

I am looking to compute correlation. I have a table with x values and y
values and would like to run something similar to the CORREL function in
EXCEL.
Does anyone have a query like that which works?

I tried the following with the numbers from the example in Excel 97 help
for CORREL:

tblXYData
ID AutoNumber
X Double
Y Double
ID X Y
1 3 9
2 2 7
3 4 12
4 5 15
5 6 17

qryR:
SELECT (1 / Sum(1)) * Sum((X - (SELECT Avg(A.X) FROM tblXYData AS A)) *
(Y - (SELECT Avg(A.Y) FROM tblXYData AS A))) / ((SELECT StDevP(A.X) FROM
tblXYData AS A) * (SELECT StDevP(A.Y) FROM tblXYData AS A)) AS rval FROM
tblXYData;

!qryR:
rval
0.997054485501581

The Excel 97 help file gives an answer of 0.997054

Note: That was the only example I tried.

James A. Fortune
(e-mail address removed)

 

[AJ]

Damn, I got all excited. I tried:
id x y
1 77981 81769
2 85892 76605
3 78902 71659
4 74935 62522
5 69517 61586
6 65301
7 70453
8 84690 62163
9 71095 68756
10 83551 65647
11 76988 73217
12 82694 76272
13 85017 66839

Excel 2003 gives me: 0.227946699

SQL query = 0.16759566544

????

 

[James A. Fortune]

Damn, I got all excited. I tried:
id x y
1 77981 81769
2 85892 76605
3 78902 71659
4 74935 62522
5 69517 61586
6 65301
7 70453
8 84690 62163
9 71095 68756
10 83551 65647
11 76988 73217
12 82694 76272
13 85017 66839

Excel 2003 gives me: 0.227946699

SQL query = 0.16759566544

????

It's almost certainly a result of the Null's. The Avg function and
probably the StDevP function doesn't count Null values in the result.
Use Nz(Whatever, 0) if you want the Null's to act like 0's or exclude
those records using WHERE X IS NOT NULL AND Y IS NOT NULL.

James A. Fortune
(e-mail address removed)