Average of n subsequent records

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Average of n subsequent records

Author

Topic

akholbert
Starting Member

4 Posts

Posted - 2009-11-28 : 07:09:20

I am using SQL server 2005 database.

I am fairly inexperienced with SQL.

I have a table containing a large amount of trend data.
Starting at the first record and ending with the last record,
I want to be able to process through the entire recordset taking the mean of N subsequent values and to return the calculated means as the result.

This trend data is to be plotted onto a graph so execution time is an issue.

Thanks in advance.

AKH

Sachin.Nand

2937 Posts

Posted - 2009-11-28 : 10:00:23

U can use the AVG function of TSQL.
select AVG(yourcolumnname) from yourtable.
Please post some sample data so that things will be more clear.

PBUH

akholbert
Starting Member

4 Posts

Posted - 2009-11-28 : 10:21:40

ID TRENDVAL
1 30
2 25
3 15
4 26
5 70
6 18
7 9
8 11

Using the example above, how would I query this table to get the average of every n rows?

i.e if n = 2

I would want the following result:

TRENDVAL
27.5 ((30 + 25) /2)
20.5 ((15 + 26) /2)
44.0 ((70 + 18) /2)
10.0 ((9 + 11) /2)

Thanks in advance.

AKH

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-28 : 10:53:51

Post the DDL of the table

You can also use centrifuges

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-28 : 13:38:13

[code]
DECLARE @n int

SET @n = 2 --(as an ex.)
SELECT AVG(TrendVal * 1.0)
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY ID) AS Seq,*
FROM Table
)t
GROUP BY (Seq-1) % @n
[/code]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-28 : 13:41:44