Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Average of top 10 in a group query.

Answered Average of top 10 in a group query.

  • Thursday, March 29, 2012 2:35 PM
     
     
    Sign In to Vote
    0

    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
     
      Has Code
    Sign In to Vote
    0

    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 svgSuresh Thursday, April 05, 2012 3:22 PM
    •  
     
  • Friday, March 30, 2012 5:17 PM
     
     
    Sign In to Vote
    0

    I think you forgot the ';'

    ;With

    I used to do that all the time.  ;)

     
  • Thursday, April 05, 2012 1:06 AM
     
     Answered Has Code
    Sign In to Vote
    1

    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,

    Please see:

    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
    Answerer
     
     Answered
    Sign In to Vote
    1

    The following article is on similar topic:

    http://www.sqlusa.com/bestpractices2008/top-group-by/

    >but couldn't find a way to get the average of the top 10 rows in a group.

    You need an outer query to do that.

    SELECT AVG .... FROM ( top 10 query ) x

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012