I have two tables: 

**ID,YRMO,Counts**

1,Dec 2013,4

1,Jan 2014,6

1,Feb 2014,7 

2,Jan,2014,6

2,Feb,2014,8

**ID,YRMO,Counts**

1,Dec 2013,10

1,Jan 2014,8

1,March 2014,12

2,Jan 2014,6 

2,Feb 2014,10

I want to find the pearson corelation coefficient for each sets of ID. There are about more than 200 different IDS.

Pearson correlation is a measure of the linear correlation (dependence) between two variables X and Y, giving a value between +1 and −1 inclusive

More can be found here :<http://oreilly.com/catalog/transqlcook/chapter/ch08.html>
at calculating correlation section
['https://Stackoverflow.com/questions/23415492', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/3325141/']
To calculate Pearson Correlation Coefficient; you need to first calculate `Mean` then `standard daviation` and then `correlation coefficient` as outlined below

1. Calculate Mean
-----------------

```
insert into tab2 (tab1_id, mean)
select ID, sum([counts]) / 
(select count(*) from tab1) as mean
from tab1
group by ID;

```

2. Calculate standard deviation
-------------------------------

```
update tab2
set stddev = (
select sqrt(
sum([counts] * [counts]) / 
(select count(*) from tab1)
- mean * mean
) stddev
from tab1
where tab1.ID = tab2.tab1_id
group by tab1.ID);

```

3. Finally `Pearson Correlation Coefficient`
--------------------------------------------

```
select ID,
((sf.sum1 / (select count(*) from tab1)
- stats1.mean * stats2.mean
)
/ (stats1.stddev * stats2.stddev)) as PCC
from (
select r1.ID,
 sum(r1.[counts] * r2.[counts]) as sum1
from tab1 r1
join tab1 r2
on r1.ID = r2.ID
group by r1.ID
) sf
join tab2 stats1
on stats1.tab1_id = sf.ID
join tab2 stats2
on stats2.tab1_id = sf.ID

```

Which on your posted data results in

![enter image description here](https://i.stack.imgur.com/xfPad.png)

See a demo fiddle here <http://sqlfiddle.com/#!3/0da20/5>

**EDIT:**

Well refined a bit. You can use the below function to get PCC but I am not getting exact same result as of your but rather getting `0.999996000000000` for `ID = 1`.

This could be a great entry point for you. You can refine the calculation further from here.

```
create function calculate_PCC(@id int)
returns decimal(16,15)
as
begin
declare @mean numeric(16,5);
declare @stddev numeric(16,5);
declare @count numeric(16,5);
declare @pcc numeric(16,12);
declare @store numeric(16,7);
select @count = CONVERT(numeric(16,5), count(case when Id=@id then 1 end)) from tab1;
select @mean = convert(numeric(16,5),sum([Counts])) / @count
from tab1 WHERE ID = @id;
select @store = (sum(counts * counts) / @count) from tab1 WHERE ID = @id;
set @stddev = sqrt(@store - (@mean * @mean)); 
set @pcc = ((@store - (@mean * @mean)) / (@stddev * @stddev)); 

return @pcc;
end

```

Call the function like

```
select db_name.dbo.calculate_PCC(1)

```
A Single-Pass Solution:
=======================

There are two flavors of the Pearson correlation coefficient, one for a Sample and one for an entire Population. These are simple, single-pass, and I believe, correct formulas for both:

```
-- Methods for calculating the two Pearson correlation coefficients
SELECT  
        -- For Population
        (avg(x * y) - avg(x) * avg(y)) / 
        (sqrt(avg(x * x) - avg(x) * avg(x)) * sqrt(avg(y * y) - avg(y) * avg(y))) 
        AS correlation_coefficient_population,
        -- For Sample
        (count(*) * sum(x * y) - sum(x) * sum(y)) / 
        (sqrt(count(*) * sum(x * x) - sum(x) * sum(x)) * sqrt(count(*) * sum(y * y) - sum(y) * sum(y))) 
        AS correlation_coefficient_sample
    FROM (
        -- The following generates a table of sample data containing two columns with a luke-warm and tweakable correlation 
        -- y = x for 0 thru 99, y = x - 100 for 100 thru 199, etc.  Execute it as a stand-alone to see for yourself
        -- x and y are CAST as DECIMAL to avoid integer math, you should definitely do the same
        -- Try TOP 100 or less for full correlation (y = x for all cases), TOP 200 for a PCC of 0.5, TOP 300 for one near 0.33, etc.
        -- The superfluous "+ 0" is where you could apply various offsets to see that they have no effect on the results
        SELECT TOP 200
                CAST(ROW_NUMBER() OVER (ORDER BY [object_id]) - 1 + 0 AS DECIMAL) AS x, 
                CAST((ROW_NUMBER() OVER (ORDER BY [object_id]) - 1) % 100 AS DECIMAL) AS y 
            FROM sys.all_objects
    ) AS a

```

As I noted in the comments, you can try the example with TOP 100 or less for full correlation (y = x for all cases); TOP 200 yields correlations very near 0.5; TOP 300, around 0.33; etc. There is a place ("+ 0") to add an offset if you like; spoiler alert, it has no effect. Make sure you CAST your values as DECIMAL - integer math can significantly impact these calcs.