Moving Average / Rolling Average

I have 2 columns in MS SQL one is Serial no. and other is values. I need the thrird column which gives me the sum of the value in that row and the next 2. Ex SNo values 1 2 2 3 3 1

Solution 1:

Here is the SQL Fiddle that demonstrates the following query:

WITH TempS as 
(
  SELECT s.SNo, s.value, 
  ROW_NUMBER() OVER (ORDERBY s.SNo) AS RowNumber
  FROM MyTable AS s
)
SELECT m.SNo, m.value,
(
  SELECT SUM(s.value) 
  FROM TempS AS s
  WHERE RowNumber >= m.RowNumber
  AND RowNumber <= m.RowNumber + 2
) AS Sum3InRow
FROM TempS AS m

In your question you were asking to sum 3 consecutive values. You modified your question saying the number of consecutive records you need to sum could change. In the above query you simple need to change the m.RowNumber + 2 to what ever you need.

So if you need 60, then use

m.RowNumber + 59

As you can see it is very flexible since you only have to change one number.

Solution 2:

In case the sno field is not sequential, you can use row_number() with aggregation:

with ss as (
      select sno, values, row_number() over (orderby sno) as seqnum
      from s
     )
select s1.sno, s1.values,
       (casewhencount(s2.values) =3thensum(s2.values) end) as avg3
from ss s1 leftouterjoin
     ss s2
     on s2.seqnum between s1.seqnum -2and s1.seqnum
groupby s1.sno, s1.values;

Solution 3:

select one.sno, one.values, one.values+two.values+three.values as thesum
from yourtable as one
left join yourtable as two
on one.sno=two.sno-1
left join yourtable as three
on one.sno=three.sno-2

Or, as requested in your comment, you could do this:

select sno, sum(values)
over (
    orderby sno
    rowsbetweencurrentrowand3 following
)
from yourtable

Solution 4:

If you need a fully generic solution, where you can sum, for example, current row + next row + 5th following row:

Step 1: Create an table listing the offsets needed. 0 = current row, 1 = next row, -1 = prev row, etc

SELECT * FROM (VALUES
  (0),(1),(2)
) o(offset)

Step 2: Use that offset table in this template (via CTE or an actual table):

WITH o AS (SELECT*FROM (VALUES (0),(1),(2) ) o(offset))
SELECT
  t1.sno,
  t1.value,
  SUM(t2.Value)
FROM@t t1
INNERJOIN@t t2 CROSSJOIN o
  ON t2.sno = t1.sno + o.offset
GROUPBY t1.sno,t1.value
ORDERBY t1.sno

Also, if SNo is not sequential, you can fetch ROW_NUMBER() and join on that instead.

WITH
  o AS (SELECT*FROM (VALUES (0),(1),(2) ) o(offset)),
  t AS (SELECT*,ROW_NUMBER() OVER(ORDERBY sno) i FROM@t)
SELECT
  t1.sno,
  t1.value,
  SUM(t2.Value)
FROM t t1
INNERJOIN t t2 CROSSJOIN o
  ON t2.i = t1.i + o.offset
GROUPBY t1.sno,t1.value
ORDERBY t1.sno