none
Average of top 10 in a group query.

    Question

  • 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.

    Thursday, March 29, 2012 2:35 PM

Answers

  • 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 1:06 AM
  • 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


    Thursday, April 05, 2012 4:40 PM

All replies

  • 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
    Thursday, March 29, 2012 3:14 PM
  • I think you forgot the ';'

    ;With

    I used to do that all the time.  ;)

    Friday, March 30, 2012 5:17 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.

    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 1:06 AM
  • 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


    Thursday, April 05, 2012 4:40 PM