Posted by sql guy123 on 07/25/06 14:21

Thank you for your information. I never learned the actual formula,
only how to click the linear regression button in excel.

Is there a way to transform the table into the correct format? Maybe a
tranverse function in SQL?

thanks


Erland Sommarskog wrote:
> 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]