SQL Average Every 5 Rows (1 Viewer)

Vagus14

Registered User.
Local time
Today, 14:32
Joined
May 19, 2014
Messages
66
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
Code:
ID	Value
1	2
2	8
3	3
4	9
5	1
6	4
7	2.5
8	6.5
9         5
10        4

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

Thank you in advance for any help.:)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:32
Joined
May 7, 2009
Messages
19,169
Use partition
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:32
Joined
May 7, 2009
Messages
19,169
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

Registered User.
Local time
Today, 14:32
Joined
May 19, 2014
Messages
66
@Arenel I'll give this a shot right away. Thank you. Let you know how it turns out.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:32
Joined
May 7, 2009
Messages
19,169
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

Registered User.
Local time
Today, 14:32
Joined
May 19, 2014
Messages
66
Works like a charm arnelgp, thanks a lot for the advice.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:32
Joined
May 7, 2009
Messages
19,169
Ur welcome ny friend.
 

Users who are viewing this thread

Top Bottom