CODEWITHSUNDEEP
sqlsql-serverpercentile
Starbucks
Starbucks

Reputation: 1558

SQL Server Querying Percentiles

I would like to query a values percentile for a table in SQL Server. For example I would like to create a variable that lists just the xth percentile, in this example lets say 90th to 100th. For these six values, the 90th percentile starts at 487.5, so only 596 should be in the new column.

ID     Age
A      215        
A      379        
A      116        
A      596        
A      177        
A      195

Desired data pull:

ID     Age    P90_100
A      215        0
A      379        0
A      116        0
A      596        596
A      177        0
A      195        0

Any help would be most appreciated.

Upvotes: 0

Views: 250

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can use ntile() and case if you really want percentiles:

select t.*,
       (case when ntile(10) over (partition by id order by age) = 10
             then age else 0
        end)
from t;

However, this does not return the results you specify.

So, I think you mean something different. That is you want 90% of the way from the minimum to the maximum value. That is not a percentile; it is a fraction of the range.

But you can calculate that as well:

select t.*,
       (case when age > min(age) over () + 0.9 * (max(age) over (partition by id) - min(age) over (partition by id))
             then age
        end)
from t;

Here is a db<>fiddle.

Upvotes: 3

Related Questions