title: SQL Server 2008 using SUM() OVER(ORDER BY...)
body: <p>I am trying to use a CTE and CROSS JOIN the result set. I want to sum up the 4 rows leading up to the current row. The example online I found does not use a CTE, only a newly created table (<a href="http://sqlandme.com/2011/08/17/sql-server-denali-over-rows-range/" rel="nofollow">http://sqlandme.com/2011/08/17/sql-server-denali-over-rows-range/</a>). The syntax should work, but I get an error saying "Incorrect syntax near 'ROWS'". </p>
  
  <p>An Example output would be this using the following statement:
  SUM(y) OVER(ORDER BY x ROWS 4 PRECEDING) sum</p>
  
  <p>X          Y          SUM       </p>
  
  <hr>
  
  <pre><code>     1          7     0     No prev rows, so sum is 0
       2          1     7     Sum   = 7 
       3          2     8           = 1 + 7
       4          5     10          = 2 + 1 + 7
       5          7     15          = 5 + 2 + 1 + 7
       6         34     15          = 7 + 5 + 2 + 1
       7         32     48          = 34 + 7 + 5 + 2
  </code></pre>
  
  <p>Does anyone have any suggestion on what is incorrect with the query? Thanks in advance.</p>
  
  <pre><code>with quarterResults as (
        &lt;subquery in here&gt;
  )
  
  --COLUMN1: String
  --COLUMN2: Date
  --COLUMN3: Date
  --COLUMN4: Double
  select a.TIC, a.DATADATE, a.EFFDATE, SUM(b.valuei) OVER (ORDER BY a.TIC, a.DATADATE, a.EFFDATE ROWS 4 PRECEDING) AS [SUM]
  from quarterResults a
  cross join quarterResults b
  where a.datadate &gt; b.datadate
  group by a.tic, a.datadate, a.EFFDATE, a.valuei
  order by a.TIC, a.datadate
  </code></pre>