Find Avg of Top 30 Results

Question

I have a query to output top 30 results

select top 30 * from [dbo].[Stats]
        where Rack_Code = 'ABC'
        order by Date_of_Record desc

and query to get averages for all time

SELECT AVG(Total_MB - (East_MB + West_MB)) AS Other,
                    AVG(East_MB) AS East,
                    AVG(West_MB) AS West
                    FROM [dbo].[Stats]
                    WHERE Rack_Code = 'ABC'

Which give correct output

But when I try to get averages for Top 30 entries, the SQL query seems to ignore Top 30

SELECT TOP 30 AVG(Total_MB - (East_MB + West_MB)) AS Other,
                    AVG(East_MB) AS East,
                    AVG(West_MB) AS West
                    FROM [dbo].[Stats]
                    WHERE Rack_Code = 'ABC'

And gives incorrect output

Please advise

Answer 1

You almost had it, all you have to do is place your TOP 30 inside sub-query or CTE and than do your AVG

SELECT AVG(Total_MB - ( East_MB + West_MB )) AS Other
   ,AVG(East_MB) AS East
   ,AVG(West_MB) AS West
FROM (
       SELECT TOP 30 *
        FROM [dbo].[stats]
        WHERE Rack_Code = 'ABC'
        ORDER BY Date_of_Record DESC
     ) a

Please note that the only time you can use ORDER BY in subquery is when you have TOP clause.

CTE solution,

;WITH   base
          AS (
               SELECT TOP 30 *
                FROM [dbo].[stats]
                WHERE Rack_Code = 'ABC'
                ORDER BY Date_of_Record DESC
             )
    SELECT AVG(Total_MB - ( East_MB + West_MB )) AS Other
           ,AVG(East_MB) AS East
           ,AVG(West_MB) AS West
        FROM base

Documentataion on CTE. Note that SQL-Server treats CTE as a view. This can introduce some overhead and if it nested deep enough then query optimizer is unable to read statistics and can create big performance drop.