Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-25-2018, 10:21 AM   #1
Vagus14
Newly Registered User
 
Join Date: May 2014
Posts: 66
Thanks: 29
Thanked 0 Times in 0 Posts
Vagus14 is on a distinguished road
SQL Average Every 5 Rows

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.

Vagus14 is offline   Reply With Quote
Old 01-25-2018, 10:45 AM   #2
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,321
Thanks: 54
Thanked 2,026 Times in 1,939 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: SQL Average Every 5 Rows

Use partition
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 01-25-2018, 10:56 AM   #3
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,321
Thanks: 54
Thanked 2,026 Times in 1,939 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: SQL Average Every 5 Rows

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);

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
Vagus14 (01-25-2018)
Old 01-25-2018, 11:02 AM   #4
Vagus14
Newly Registered User
 
Join Date: May 2014
Posts: 66
Thanks: 29
Thanked 0 Times in 0 Posts
Vagus14 is on a distinguished road
Re: SQL Average Every 5 Rows

@Arenel I'll give this a shot right away. Thank you. Let you know how it turns out.
Vagus14 is offline   Reply With Quote
Old 01-25-2018, 11:14 AM   #5
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,321
Thanks: 54
Thanked 2,026 Times in 1,939 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: SQL Average Every 5 Rows

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;
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
Vagus14 (01-25-2018)
Old 01-25-2018, 11:39 AM   #6
Vagus14
Newly Registered User
 
Join Date: May 2014
Posts: 66
Thanks: 29
Thanked 0 Times in 0 Posts
Vagus14 is on a distinguished road
Re: SQL Average Every 5 Rows

Works like a charm arnelgp, thanks a lot for the advice.
Vagus14 is offline   Reply With Quote
Old 01-25-2018, 11:41 AM   #7
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,321
Thanks: 54
Thanked 2,026 Times in 1,939 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: SQL Average Every 5 Rows

Ur welcome ny friend.


__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Reply

Tags
5 rows , access 2013 , average , sql

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Looking to do a yearly average vs. weekly average Jason1 Queries 1 10-30-2014 12:11 PM
Calc Average from selected rows vae Queries 2 04-28-2011 05:02 PM
Vertical rows into horizontal rows comma separated per group K-CJ Modules & VBA 4 08-28-2010 03:26 PM
Getting an average of multiple columns/rows Ken1138 Forms 0 07-02-2008 07:15 AM
What About This Average?...Field Average... johann201 Queries 2 02-19-2007 12:15 PM




All times are GMT -8. The time now is 06:39 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World