Select Average of Top 25% of Values in SQL

sql, sql-server, stored-procedures, t-sql

Solution

Here's what I did, given the table shown above:

select AVG(t.Value)
from (select top 25 percent Value
      from @TempGroupTable
      where FormulaID = @PassedInFormulaID
      order by Value desc) as t

The `desc` must be there, because the `percent` command will not actually do comparisons. It will just simply grab the first x number of records, with x being equal to 25% of the count of records it's querying. Therefore, the `order by Value desc` line then will grab the top 25% records which have the highest `Value`, and then sends that info to be averaged.

As a side note to all of this, this also means that if you wanted to grab the bottom 25% instead, or if your formula results are like a golf score (i.e. lowest is the best), all you would need to do is remove the `desc` part and you would be good to go.

Problem

I'm currently writing a stored procedure for my client to populate some tables that will be used to generate SSRS reports later on. Some of the data is based on specific stock formulas that are run on each of their clients' quarterly data (sent to them by their clients). The other part of the data is generated by comparing those results against those from other, similar sized clients. One of the things that they want tracked in their reports is the average of the top 25% of formula results for that particular comparison group. To give a better picture of it, imagine the following fields that I have in a temp table: ``` FormulaID int Value decimal (18,6) ``` I want to do the following: Given a specific `FormulaID` return the average of the top 25% of `Value`. I know how to take an average in SQL, but I don't know how to do it against only the top 25% of a specific group. How would I write this query?

Original source