Linear Regression for column values

[sql guy123]

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?

thanks

[Erland Sommarskog]

sql guy123 (st*****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[sql guy123]

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 (st*****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

sql guy123 (st*****@hotmail.com) writes:

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?

There is an UNPIVOT operator in SQL 2005. But it's mainly syntactic
sugar, and you can easily unpivot without it:

SELECT year, value = CASE year WHEN 2001 THEN [2001]
WHEN 2002 THEN [2002]
...
END
FROM tbl
CROSS JOIN (SELECT 2001 UNION ALL
SELECT 2002 UNION ALL
...) AS year
My thought, though, was that you would redesign the table on a
permanent basis.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx