Covariance Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kbarlak@gmail.com - 23 Jun 2006 04:11 GMT

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.

Reply to this Message

Michel Walsh - 01 Jul 2006 12:34 GMT

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

>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.

Reply to this Message

Gary Walter - 01 Jul 2006 15:00 GMT

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)

> You can SUM over an expression, like
>
[quoted text clipped - 16 lines]
>> correllation). I can use excel functions in Access but i couldnt figure
>> out how can i create arrays to use this function.

Reply to this Message

Michel Walsh - 01 Jul 2006 15:34 GMT

Hi,

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

Vanderghast, Access MVP

> Thanks Michel...
>
[quoted text clipped - 70 lines]
>>> correllation). I can use excel functions in Access but i couldnt figure
>>> out how can i create arrays to use this function.

Reply to this Message