Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author 
Topic 
billsox
Yak Posting Veteran
74 Posts 
Posted  20021126 : 16:45:51

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

baldeep
Starting Member
18 Posts 
Posted  20021126 : 17:59:07

I believe you can create a Data Mining Model to do this using Analysis Services.
Baldeep



robvolk
Most Valuable Yak
15732 Posts 
Posted  20021126 : 18:23:56

Damn! And I just figured out how to do it in TSQL:
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 select ((@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:
http://www.futuresource.com/industry/lin.asp
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 yintercept; my code uses this convention.



billsox
Yak Posting Veteran
74 Posts 
Posted  20021127 : 10:32:12

That's okay, Rob. Using the Analysis Services solution wouldn't have worked for me since I needed a TSQL solution. I actually did what you did... scripted a regression function from scratch. Thanks!
Bill 


linus1976
Starting Member
1 Post 



