How to construct this query in T-SQL

Question

I have a table:

ID A    B    C    D
1  10   20   30   5
2  332  80   32   12
3  41   20   82   42
.
.
.

I want to query that gives me

A   B   C   D

Where A contains the average of column A for the top 30 rows of the table, sorted by ID ascending, B contains the average of column B for the top 30 rows of the table, sorted by ID ascending, etc.

If table has less than 30 rows, than still take the average, but average across however many rows it has.

Answer 1

Try this:

SELECT AVG (A), AVG(B), AVG(C), AVG(D)
FROM ThisTable tt
WHERE tt.ID IN (SELECT TOP 30 ID FROM ThisTable ORDER BY ID ASC)

Answer 2

First pick out the 30 rows, the select from that to get the averages:

select avg(A), avg(B), avg(C), avg(D)
from (
   select top 30 A, B, C, D
   from TheTable
   order by ID
) x

Answer 3

I'm not going to do the whole query (something like select top 30 avg(a), avg(b), etc) should work, but to take care of the ordering, it's probably simplest to make sure your ID column is a clustered index which will ensure your ordering.

The clustered index is of course a precious resource, you need to evaluate if you need it somewhere else.

*** ignore my "answer", it is incorrect (see comments below)