Covariance Query

  • Thread starter Thread starter kbarlak
  • Start date Start date
K

kbarlak

I have multiple columns with data and like to calculate covariance (or
correllation). I can use excel functions in Access but i couldnt figure
out how can i create arrays to use this function.
 
Hi,


You can SUM over an expression, like


SELECT SUM( table1.x * table2.y)
FROM table1 INNER JOIN table2 ON table1.dateStamp = table2.dateStamp


where dateStamp is the field that identify the field to be used to "match"
the two tables, row by row.



http://mathworld.wolfram.com/Covariance.html mentions, eq 12, that the
covariance can be given by:


SUM( table1.x * table2.y)
- (SELECT AVG(x) FROM table1) * (SELECT AVG(y) FROM table2)



Hoping it may help,
Vanderghast, Access MVP
 
Thanks Michel...

I searched the web and the only thing
I found (that I could understand) was

http://www.oreilly.com/catalog/transqlcook/chapter/ch08.html

where they cover *correlation coefficient*
in terms I can understand:

***quote***
Calculating Correlation
Problem
You want to calculate the correlation between two samples.
For example, you want to calculate how similar the light-bulb
sales patterns are for two different years.

Solution
The query in the following example uses the formula for
calculating correlation coefficients shown earlier in this chapter.
It does this for the years 1997 and 1998.

SELECT
(COUNT(*)*SUM(x.Sales*y.Sales)
-SUM(x.Sales)*SUM(y.Sales))
/
(SQRT(COUNT(*)*SUM(SQUARE(x.Sales))
- SQUARE(SUM(x.Sales)))*
SQRT(COUNT(*)*SUM(SQUARE(y.Sales))
- SQUARE(SUM(y.Sales))))
correlation
FROM
BulbSales x JOIN BulbSales y
ON x.month=y.month
WHERE
x.Year=1997
AND
y.Year=1998

correlation
-----------------------------------------------------
0.79

The correlation calculated is 0.79,
which means that the sales patterns between the two years
are highly correlated or, in other words, very similar.
***unquote***

I wish they had covered covariance as well as they did correlation
(or maybe they did, but when they got into SQL pivot tables
I gave up on an Access solution)....

thanks again (not OP)
 
Hi,


Note I should have said equation 2, not 12, to be strictly speaking.


Vanderghast, Access MVP
 
Back
Top