SQL Average Every 5 Rows

[Vagus14]

Hey all,

I am running Access 2013 (fun version where they took out the pivot tables and query charts)

I want to average every five rows and I was wondering if someone could help, here is the data (SQL):

Table Name: tblTest

ID	Value
1	2
2	8
3	3
4	9
5	1
6	4
7	2.5
8	6.5
9         5
10        4

Desired results:
ID       Value
1-5      4.6
6-10     4.4

Thank you in advance for any help.

 

[arnelgp]

Use partition

 

[arnelgp]

SELECT partition(Table1.ID,1,DCOUNT("*","TABLE1"),5) AS ID, Avg(Table1.value) AS [VALUE]
FROM Table1
GROUP BY partition(Table1.ID,1,DCOUNT("*","TABLE1"),5);

 

[Vagus14]

@Arenel I'll give this a shot right away. Thank you. Let you know how it turns out.

 

[arnelgp]

If you want to replace the colon) with a dash(-):

SELECT REPLACE(T1.ID,":","-") AS ID, T1.[VALUE] FROM (SELECT partition(TABLE1.ID,1,DCOUNT("*","TABLE1"),5) AS ID, Avg(TABLE1.value) AS [VALUE]
FROM TABLE1
GROUP BY partition(TABLE1.ID,1,DCOUNT("*","TABLE1"),5)) AS T1;

 

[Vagus14]

Works like a charm arnelgp, thanks a lot for the advice.

 

[arnelgp]

Ur welcome ny friend.