Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 linear regression

Author  Topic 

Yak Posting Veteran

74 Posts

Posted - 2002-11-26 : 16:45:51

I'm looking to see if anyone knows how to perform linear regression in T-SQL. Haven't found anything online. I know FMS sells Total SQL Statistics which contains the T-SQL code for linear regression but I'm not exactly willing to drop $999 for a license. Any help?


Starting Member

18 Posts

Posted - 2002-11-26 : 17:59:07
I believe you can create a Data Mining Model to do this using Analysis Services.


Go to Top of Page

Most Valuable Yak

15732 Posts

Posted - 2002-11-26 : 18:23:56
Damn! And I just figured out how to do it in T-SQL:

set nocount on
declare @n int
create table #lr (x real not null, y real not null)
--insert sample data
insert into #lr values (0,1)
insert into #lr values (4,9)
insert into #lr values (2,5)
insert into #lr values (3,7)

select @n=count(*) from #lr
--this is just for convenience, you can substitute count(*) for @n in the code

--meat of the code begins here
((@n * sum(x*y)) - (sum(x)*sum(y)))/
((@n * sum(Power(x,2)))-Power(Sum(x),2)) AS M,
avg(y) - ((@n * sum(x*y)) - (sum(x)*sum(y)))/
((@n * sum(Power(x,2)))-Power(Sum(x),2)) * avg(x) as B
from #lr

drop table #lr

This is the most basic least squares method, based on the formula posted here:

The formal terminology differs from what's presented on the page, the formula should be y = mx + b, where m is the slope and b is the y-intercept; my code uses this convention.

Go to Top of Page

Yak Posting Veteran

74 Posts

Posted - 2002-11-27 : 10:32:12
That's okay, Rob. Using the Analysis Services solution wouldn't have worked for me since I needed a T-SQL solution. I actually did what you did... scripted a regression function from scratch. Thanks!

Go to Top of Page

Starting Member

1 Post

Posted - 2011-11-17 : 23:33:48
Here is a pretty neat solution using cursor to perform rolling regression over a dataset:
Go to Top of Page

- Advertisement -