Clever Geek Handbook

Find 30 Average Results

I have a request to display 30 results

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

and query to get all-time averages

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 gives the correct conclusion

enter image description here

But when I try to get the averages for the Top 30 records, the SQL query seems to be ignoring 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 the wrong conclusion

enter image description here

Please inform

+3


source to share


1 answer


You had it pretty much everything, all you have to do is place TOP 30

yours inside a subquery or CTE, not yoursAVG 

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

Note that the only time you can use ORDER BY

in a subquery is with a suggestion TOP

.



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

CTE documentation . Note that SQL-Server treats the CTE as a view. This can lead to some overhead, and if it is nested deeply enough the query optimizer will not be able to read the statistics and can greatly increase performance.

+6


source






More articles:


All Articles