# average values of per 5 rows

• ### Question

• hello
I have a column named 'number'.
it has many rows. I want to get the average value of per 5 rows. if I have 100 row I need to get the 20 row that every row is the average of the 5 rows sequently.  Can I do that?
example
I have table like this:
10
20
30
20
10
50
60
70
60
50
I want to get the result this way:
18  ' average of first 5 row
58  'average of the other 5 row

thanks

• Edited by Friday, January 2, 2009 12:21 PM
Thursday, January 1, 2009 9:36 PM

• 30 and 70 aren't averages.  They're the max numbers for the range, but your averages are really:

(10+20+30+20+10)/5 = 18
and
(50+60+70+60+50)/5 = 58

Are you sure you're looking for averages?  If you are, you could use something like this:

-- Set up test data

DECLARE @TestAggregate TABLE

(RowOrder int IDENTITY (1,3) PRIMARY KEY CLUSTERED,

Value int NOT NULL)

INSERT @TestAggregate (Value)

SELECT 10

UNION ALL SELECT 20

UNION ALL SELECT 30

UNION ALL SELECT 20

UNION ALL SELECT 10

UNION ALL SELECT 50

UNION ALL SELECT 60

UNION ALL SELECT 70

UNION ALL SELECT 60

UNION ALL SELECT 50

--Use a CTE to produce sequential row numbers

--(to make up for any "gaps" in the ordering column)

;WITH OrderedRows (RowNumber, Value)

AS    (SELECT ROW_NUMBER() OVER (ORDER BY RowOrder ASC), Value

FROM  @TestAggregate)

SELECT   (RowNumber + 4)/5 AS RowGroup, AVG(CAST(Value AS decimal(15,3)))  AS AverageValue

FROM     OrderedRows

GROUP BY (RowNumber + 4)/5

Does this help?
Aaron Alton | thehobt.blogspot.com
• Proposed as answer by Friday, January 2, 2009 5:07 AM
• Marked as answer by Friday, January 2, 2009 12:53 PM
Thursday, January 1, 2009 10:55 PM
•  ; WITH OrderedRows AS ( SELECT (Row_Number() OVER (ORDER BY no ASC) - 1) / 5 As [n] , number FROM   Table1 ) SELECT Max(number) , n FROM   OrderedRows GROUP BY n

George
• Edited by Friday, January 2, 2009 12:24 PM Max, not Avg!
• Marked as answer by Friday, January 2, 2009 12:27 PM
Friday, January 2, 2009 12:22 PM
•
 SELECT Max(number) As [lets_give_this_column_a_name] ...

George
• Marked as answer by Friday, January 2, 2009 1:21 PM
Friday, January 2, 2009 12:56 PM
•  ; WITH OrderedRows AS ( SELECT (Row_Number() OVER (ORDER BY no ASC) - 1) / 5 As [n] , number, number2, number FROM   Table1 ) SELECT N, Max(number) Number, Max(number1) Number1, Max(number2) Number2, FROM OrderedRows GROUP BY N

I am Back..!
• Marked as answer by Monday, January 5, 2009 8:59 AM
Monday, January 5, 2009 8:34 AM

### All replies

• 30 and 70 aren't averages.  They're the max numbers for the range, but your averages are really:

(10+20+30+20+10)/5 = 18
and
(50+60+70+60+50)/5 = 58

Are you sure you're looking for averages?  If you are, you could use something like this:

-- Set up test data

DECLARE @TestAggregate TABLE

(RowOrder int IDENTITY (1,3) PRIMARY KEY CLUSTERED,

Value int NOT NULL)

INSERT @TestAggregate (Value)

SELECT 10

UNION ALL SELECT 20

UNION ALL SELECT 30

UNION ALL SELECT 20

UNION ALL SELECT 10

UNION ALL SELECT 50

UNION ALL SELECT 60

UNION ALL SELECT 70

UNION ALL SELECT 60

UNION ALL SELECT 50

--Use a CTE to produce sequential row numbers

--(to make up for any "gaps" in the ordering column)

;WITH OrderedRows (RowNumber, Value)

AS    (SELECT ROW_NUMBER() OVER (ORDER BY RowOrder ASC), Value

FROM  @TestAggregate)

SELECT   (RowNumber + 4)/5 AS RowGroup, AVG(CAST(Value AS decimal(15,3)))  AS AverageValue

FROM     OrderedRows

GROUP BY (RowNumber + 4)/5

Does this help?
Aaron Alton | thehobt.blogspot.com
• Proposed as answer by Friday, January 2, 2009 5:07 AM
• Marked as answer by Friday, January 2, 2009 12:53 PM
Thursday, January 1, 2009 10:55 PM
• If its MAX then same can be modified as below. Choice is yours :)

 DECLARE @TestAggregate TABLE (RowOrder int IDENTITY (1,3) PRIMARY KEY CLUSTERED, Value int NOT NULL) INSERT @TestAggregate (Value) SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT 30 UNION ALL SELECT 20 UNION ALL SELECT 10 UNION ALL SELECT 50 UNION ALL SELECT 60 UNION ALL SELECT 70 UNION ALL SELECT 60 UNION ALL SELECT 50 --Use a CTE to produce sequential row numbers --(to make up for any "gaps" in the ordering column) ;WITH OrderedRows (RowNumber, Value) AS    (SELECT ROW_NUMBER() OVER (ORDER BY RowOrder ASC), Value FROM  @TestAggregate) SELECT   (RowNumber + 4)/5 AS RowGroup, max(Value)  AS AverageValue FROM     OrderedRows GROUP BY (RowNumber + 4)/5

Friday, January 2, 2009 6:46 AM
• If you want the "middle" value of each group of 5 rows (i.e. the third value in each group), you can modify the above like this:

 DECLARE @TestAggregate TABLE (RowOrder int IDENTITY (1,3) PRIMARY KEY CLUSTERED, Value int NOT NULL); INSERT @TestAggregate (Value) SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT 30 UNION ALL SELECT 20 UNION ALL SELECT 10 UNION ALL SELECT 50 UNION ALL SELECT 60 UNION ALL SELECT 70 UNION ALL SELECT 60 UNION ALL SELECT 50; WITH OrderedRows (RowNumber, Value) AS (SELECT ROW_NUMBER() OVER (ORDER BY RowOrder ASC), Value FROM  @TestAggregate) SELECT Value FROM OrderedRows WHERE RowNumber % 5 = 3

Razvan
Friday, January 2, 2009 9:39 AM
•

Make sure your source table already have cluster index on different column, use that column in the ORDER BY clause. Otherwise you may end-up with wrong output.

I am Back..!
Friday, January 2, 2009 9:59 AM
• I think you mean:

Make sure that your source table already has another column that denotes the sequence so you can use that column in the ORDER BY clause.

Having a clustered index is a red-herring.
George
Friday, January 2, 2009 10:14 AM
•

Yes.. I meant the same. who keeps the order/sequence.

I am Back..!
Friday, January 2, 2009 11:27 AM
•  Thank you everyone for the replies. I wrote wrong averages Aaron you are right. Aaron your code does exactly what I wanted but because of I am completely new to transact sql, I couldnt adapt this code my table. I think first part of your code you create some data to work on. Second part where starts ;WITH you are getting the data.  Could you please modify the code for my table I defined:
table name : Table1
I have two column.  No and Number. No is the Identity column and primary key. Number has the values.

No    number
1        10
2        20
3        30
4        20
5        10
6        50
7        60
8        70
9        60
10      50
...       ...

Friday, January 2, 2009 12:14 PM
• In fact, aren't 30 and 70 the medians of each range of 5 numbers (i.e. the 3rd of every 5th)?

In which case:
 DECLARE @your_table table ( seq   int , value int ) --seq is an arbitrary column that simply denotes the ORDER of the values INSERT INTO @your_table (seq, value) SELECT  1, 10 UNION SELECT  2, 20 UNION SELECT  3, 30 UNION SELECT 15, 20 UNION SELECT 21, 10 UNION SELECT 43, 50 UNION SELECT 44, 60 UNION SELECT 67, 70 UNION SELECT 97, 60 UNION SELECT 99, 50 --the 3rd column gives a sequential number based on the order of seq --the 4th column splits this sequential number into groups of 5 SELECT seq , value , Row_Number() OVER (ORDER BY seq) As [n] , (Row_Number() OVER (ORDER BY seq) - 1) / 5 As [the_groups] FROM   @your_table --So now that we have the groups worked out, we can work out our medians from here! --To do this we make the above query intoa  subquery and use Row_Number() again to --partition by each group SELECT value , Row_Number() OVER (PARTITION BY the_groups ORDER BY seq) As [partitioned_sequence] FROM   ( SELECT seq , value , (Row_Number() OVER (ORDER BY seq) - 1) / 5 As [the_groups] FROM   @your_table ) As [a_subquery] --Finally, we add a WHERE clause to filter to the 3rd record iin each partition (to do this --we must subquery one more time!) SELECT value FROM   ( SELECT value , Row_Number() OVER (PARTITION BY the_groups ORDER BY seq) As [partitioned_sequence] FROM   ( SELECT seq , value , (Row_Number() OVER (ORDER BY seq) - 1) / 5 As [the_groups] FROM   @your_table ) As [a_subquery] ) As [another_subquery] WHERE  partitioned_sequence = 3

You need to understand what results you get when you do not have a number of results that is not a product of 5 (e.e if you have 97 results, and the records 95-97 where 1, 2, 3 & 4, what would the median be and why?

Anyway, hope this helps!
George
Friday, January 2, 2009 12:19 PM
•  ; WITH OrderedRows AS ( SELECT (Row_Number() OVER (ORDER BY no ASC) - 1) / 5 As [n] , number FROM   Table1 ) SELECT Max(number) , n FROM   OrderedRows GROUP BY n

George
• Edited by Friday, January 2, 2009 12:24 PM Max, not Avg!
• Marked as answer by Friday, January 2, 2009 12:27 PM
Friday, January 2, 2009 12:22 PM
• thanks george. I have a column with 'no column name' in the result any suggestion for this?
Friday, January 2, 2009 12:53 PM
•
 SELECT Max(number) As [lets_give_this_column_a_name] ...

George
• Marked as answer by Friday, January 2, 2009 1:21 PM
Friday, January 2, 2009 12:56 PM
• if we want to do this for multiple rows. for example 2 of these 3 rows?

No    number     number1    number2
1        10              30              40
2        20              50              50
3        30              60              60
4        20              70              70
5        10              80              80
6        50              90              90
7        60              100            100
8        70              110            110
9        60              120            120
10      50              130            130
...       ...               ...               ...
Monday, January 5, 2009 8:12 AM
•  ; WITH OrderedRows AS ( SELECT (Row_Number() OVER (ORDER BY no ASC) - 1) / 5 As [n] , number, number2, number FROM   Table1 ) SELECT N, Max(number) Number, Max(number1) Number1, Max(number2) Number2, FROM OrderedRows GROUP BY N

I am Back..!
• Marked as answer by Monday, January 5, 2009 8:59 AM
Monday, January 5, 2009 8:34 AM