Average of top 10 in a group query.

# Average of top 10 in a group query.

• Thursday, March 29, 2012 2:35 PM

I wanted to write a query to get me the average of the top 10 rows in each group.

I tried using the rank function to get the top 10 rows in a group,but couldn't find a way to get the average of the top 10 rows in a group.

### All Replies

• Thursday, March 29, 2012 3:14 PM

Hi Mohnish,

Please see the code below along with query at the end, hope this helps.

```create
table #temp1 (ID int,ServiceNum varchar(10),Code int)
insert
into #temp1 values(100,'34',0)
insert
into #temp1 values(100,'35',0)
insert
into #temp1 values(100,'36',0)
insert
into #temp1 values(100,'37',0)
insert
into #temp1 values(100,'38',0)
insert
into #temp1 values(102,'67',20)
insert
into #temp1 values(102,'31',35)
insert
into #temp1 values(102,'25',35)
insert
into #temp1 values(102,'78',0)
insert
into #temp1 values(103,'25',3)
insert
into #temp1 values(103,'29',1)
select * from #temp1 order by ID, Code
; with cte as(
SELECT  id, servicenum
,ROW_NUMBER() OVER(PARTITION BY id ORDER BY  servicenum) AS 'ROW_NUM'
from #temp1
)
select * from cte Where ROW_NUM < 3 --replace 3 with 10 if you need top 10 in the group```

Thanks,
Suresh
Please use Mark or Propose as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

• Edited by Thursday, April 05, 2012 3:22 PM
•
• Friday, March 30, 2012 5:17 PM

I think you forgot the ';'

;With

I used to do that all the time.  ;)

• Thursday, April 05, 2012 1:06 AM

I wanted to write a query to get me the average of the top 10 rows in each group.

I tried using the rank function to get the top 10 rows in a group,but couldn't find a way to get the average of the top 10 rows in a group.

Hi Mohnish Khiani,

```declare @table table (id int,groupid int,qty int)

insert into @table values (1,1,50)
insert into @table values (2,1,10)
insert into @table values (3,1,10)
insert into @table values (4,1,1000)
insert into @table values (5,2,10)
insert into @table values (6,2,10)
insert into @table values (7,2,20)
insert into @table values (8,2,1000)
insert into @table values (9,3,10)
insert into @table values (10,3,20)
insert into @table values (11,3,30)
insert into @table values (12,3,1000)

;with cte
as
(
select id,groupid,qty,
row_number() over (partition by groupid order by id) as 'rownum'
from @table
)

select groupid,avg(qty) from cte
where rownum<4
group by groupid

-- or

select groupid,avg(qty)
from
(
select t1.id,t1.groupid,t1.qty
from @table t1
where t1.id in (select top 3 t2.id from @table t2 where t2.groupid = t1.groupid)
) t
group by groupid```
If it's not you want, please show us the table DDL, sample data and your expected result.

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Get or Request Code Sample from Microsoft
If you have any feedback, please tell us.

• Thursday, April 05, 2012 4:40 PM