# Find average of odd numbers

176 Reputation points
2022-02-23T18:00:53.05+00:00

Hi All,
Please I have the below table structure

With values like below

The record count at any time will always be in multiple of 3 that is 3 or 6 or 9 or 12 or 15 or 18 or 21 etc.
Please can I get a query that can find the average weight of every 3 records with equivalent row number like below

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,625 questions

1. 10,791 Reputation points
2022-02-23T19:08:45.323+00:00

You can try the following. Thanks!

``````DECLARE @test TABLE (ID INT IDENTITY, Weight decimal(18, 5));

insert into @test values('10');
insert into @test values('10');
insert into @test values('10');
insert into @test values('11');
insert into @test values('11');
insert into @test values('11')
insert into @test values('12')
insert into @test values('12')
insert into @test values('12')
insert into @test values('13')
insert into @test values('13')
insert into @test values('13')
insert into @test values('14')
insert into @test values('14')
insert into @test values('14')

select SN, avg(Weight) as AverageWeight from
(select *, (id+2)/3 as SN  from @test) as t
group by SN
``````

1. 116.6K Reputation points
2022-02-23T18:44:41.163+00:00

Check a query:

``````;
with Q as ( select *, ntile(3) over (order by ID) as SN from Table)
select SN, avg(Weight) as AverageWeight from Q group by SN
``````

If it does not work, then show how to divide the numbers into three groups.