select avg(top 5 columnname) where condition???

• ZA_Crafty

  Ten Centuries

  Points: 1228

  More actions

  June 28, 2008 at 3:33 am

  #75252

  can I get the Avg of top 5 records?

  When you say it like this, it sounds simple..... but try it....

  create table #test(col1 int, status int)

  INSERT INTO #test (col1, status) VALUES (1,1)

  INSERT INTO #test (col1, status) VALUES (2,1)

  INSERT INTO #test (col1, status) VALUES (3,1)

  INSERT INTO #test (col1, status) VALUES (4,1)

  INSERT INTO #test (col1, status) VALUES (5,1)

  INSERT INTO #test (col1, status) VALUES (6,1)

  INSERT INTO #test (col1, status) VALUES (7,1)

  INSERT INTO #test (col1, status) VALUES (8,1)

  INSERT INTO #test (col1, status) VALUES (9,1)

  INSERT INTO #test (col1, status) VALUES (10,2)

  INSERT INTO #test (col1, status) VALUES (11,2)

  INSERT INTO #test (col1, status) VALUES (12,2)

  INSERT INTO #test (col1, status) VALUES (13,2)

  INSERT INTO #test (col1, status) VALUES (14,2)

  INSERT INTO #test (col1, status) VALUES (15,2)

  INSERT INTO #test (col1, status) VALUES (16,2)

  INSERT INTO #test (col1, status) VALUES (17,2)

  INSERT INTO #test (col1, status) VALUES (18,2)

  INSERT INTO #test (col1, status) VALUES (19,2)

  INSERT INTO #test (col1, status) VALUES (20,2)

  select * from #test

  Select AVG(ALL,(Select top 5 Col1 where Status = 2))

  Drop table #test

• Ben Lloyd-417375

  SSC Journeyman

  Points: 80

  More actions

  June 28, 2008 at 4:07 am

  #835737

  Probably easiest to use a subquery to get the top 5 first, then average the result:

  SELECT AVG( col1 )

  FROM ( SELECT TOP 5 col1 FROM #test WHERE status = 2 ) AS t

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  June 28, 2008 at 8:22 am

  #835758

  Note that "TOP 5" is not really meaningful in this context because you have not defined an order.

  [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
  Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736068

  More actions

  June 28, 2008 at 8:39 am

  #835764

  You need a subquery to get the top 5. You could use a CTE as well or a view.

  Basically get the top 5 records

  select top 5 xx from table yyyyy order by zz (or whatever makes sense)

  Then you need to select from this result set and get the average.

  If it were a view, it's easy, select avg(xx) from view. CTE, you define that first and then select from it like a view. We've got a couple interesting articles on CTEs.

  http://www.sqlservercentral.com/articles/Development/commontableexpressionsinsqlserver2005/1758/

  Or use the subquery above, but add an order by