Posted by Erland Sommarskog on 07/24/06 22:02

sql guy123 (stoppal@hotmail.com) writes:
> This is a real challenge. I hope someone is smart enough to know how
> to do this.
>
> I have a table
>
> TABLE1
> [Column 1- 2001]
> [Column 2- 2002]
> [Column 3- 2003]
> [Column 4 - 2004]
> [Column 5 - 2005]
> [Column 6 - 2006]
> [Column 7 - Slope]
>
>
> [2001][2002][2003][2004][2005][2006] [Slope]
> [1] [2] [3] [4] [5] [6] [1]
> [1.2] [.9] [4] [5] [5.4] [6.2] [?]
>
>
> Slope is defined as "M" in the equation y=mx+b
>
> I need a way a finding the linear equation that best fits the points so
> I can have SQL calculate the slope.
>
> Are there any smart people around that would know how to do this?

Smart? Either you did learn linear regression in school or you didn't.

I will have to admit that although I taught mathematical statistics as a
student, that I've forgotten the forumulas. But I looked them up in my
statisticcs book:

m = SUM((Xi - AVG(X))*(Yi - AVG(Y)) / SUM(SQR(Xi - AVG(X))

where Xi and Yi are the individual values for the X and Y variables.

For the computation in SQL you can of course not use SUM and AVG since
your table is turned the wrong way. The years should have been columns
instead.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]