CODEWITHSUNDEEP
sqlsql-serveraverage
user3347733
user3347733

Reputation: 3

SQL Server top nth average

I tried to find average value of top nth row in SQL,

but found some interesting output of my query. I ma not sure why I am not getting my desired output.

Here is my query 

DECLARE @myTable TABLE
        (
             rowNum int not null
       , ResponseID int not null
       , AverageValue decimal(18,2) not null
        )

INSERT  @myTable
VALUES  (1,1,1),
        (1,1,2),
        (1,1,2),
        (1,1,9),
        (1,1,3),
        (1,1,8),
        (1,1,10),
        (1,1,3)

SELECT * FROM @myTable
SELECT TOP 7 * FROM @myTable

SELECT TOP 7 AVG(averageValue) FROM @myTable

SELECT (1.00+2.00+2.00+9.00+3.00+8.00+10.00)/7

Please anyone can tell why I am getting different value?

Thanks Amit Patel.

Upvotes: 0

Views: 69

Answers (1)

Dmitriy Khaykin
Dmitriy Khaykin

Reputation: 5258

You're not averaging the top 7, you're selecting the top 7 rows of the average of the averageValue column.

You're currently saying,:

"give me the first 7 results of the average of the averageValue column"

Since AVG() is an aggregate function, there is only one result!

What you want to say is:

"give me the the average of the first 7 results of the averageValue column"

To do that, you need a subquery to only average the top 7 rows (and get one average):

SELECT AVG(averageValue) FROM 
( Select Top 7 averagevalue from @myTable ) t
-- returns 5.00000; 1 rows affected.

Upvotes: 3

Related Questions