title: Rolling averages in SQL Server 2012 using range
body: <p>I’m trying to calculate a 3 month rolling average grouped by region and month, as in</p>
  
  <pre><code>Region  Month                 Avg(var_a)    Avg(var_b)
  Northland   Dec-Jan-Feb       7.1           5.9
  Southland   Dec-Jan-Feb       7.2               6.1
  Northland   Nov-Dec-Jan       7.4           6.1
  Southland   Nov-Dec-Jan       7.5           6.2
  Northland   Oct-Nov-Dec       7.5               6.2
  Southland   Oct-Nov-Dec       7.5           6.1
  </code></pre>
  
  <p>Note that month is expanded for illustrative purposes, I’d really expect the output to just say a single month.</p>
  
  <p>Now I can do this by creating a CTE grouping by region and month, then joining to it a couple times like</p>
  
  <pre><code>With month_rollup_cte as
      (Select region,month,sum(var_a) a_sum, sum(var_b) b_sum, count(1) cnt
  From vw_score_by_region
      Group by region,month)
  Select c1.region, c1.month,sum(c1.a_sum + c2.a_sum + c3.a_sum) / sum(c1.cnt + c2.cnt + c3.cnt) a_avg, sum(c1.b_sum + c2.b_sum + c3.b_sum) / sum(c1.cnt + c2.cnt + c3.cnt) b_avg
  From month_rollup_cte c1
  Join month_rollup_cte c2 on c1.region = c2. Region and c1.month = dateadd(mm,1,c2.month)
  Join month_rollup_cte c3 on c1.region = c3. Region and c1.month = dateadd(mm,2,c3.month)
  Group by c1.region, c1.month;
  </code></pre>
  
  <p>But that’s ugly, imagine if you had to do a 6 month rolling average or 12 month rolling average… I’m trying to use the t-sql 2012 analytic functions, specifically the RANGE option.  I’ve used ROWS preceding before, but never range.</p>
  
  <p>What I tried was</p>
  
  <pre><code>select region,avg(var_a) OVER (order by (year(entry_month) * 100 + month(entry_month)) range between 2 preceding and 1 following)    
  from [dbo].[vw_score_by_region]
  group by region
  </code></pre>
  
  <p>But I get a syntax error:</p>
  
  <blockquote>
    <p>*Msg 8120, Level 16, State 1, Line 2<br>
    Column 'dbo.vw_score_by_region.month' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.*</p>
  </blockquote>
  
  <p>Clearly I'm doing something silly, but I'm not sure what.</p>