Analytics
Highlighted
Fan

## How to calculate 90th percentile in Teradata

Seems no build in available in tera data to calculate 90th or nth percentile for the given dataset
unlike ORACLE
2 REPLIES
Enthusiast

## Re: How to calculate 90th percentile in Teradata

Teradata is slightly more generic - it uses QUANTILE which allows you to split into groups based on any fraction.
So 90th percentile is:

Select
blah,blah,blah
From somewhere
Qualify Quantile(100,col1) = 90
;
Enthusiast

## Re: How to calculate 90th percentile in Teradata

this is what i use. ansi, so you can use it in oracle or td. can easily be expanded out to give you the entire distibution should you want.

SELECT
col
, MAX(CASE WHEN percentile <=10 THEN pctl_var END) AS percentile_10
, MAX(CASE WHEN percentile <=20 THEN pctl_var END) AS percentile_20
, MAX(CASE WHEN percentile <=30 THEN pctl_var END) AS percentile_30
, MAX(CASE WHEN percentile <=40 THEN pctl_var END) AS percentile_40
, MAX(CASE WHEN percentile <=50 THEN pctl_var END) AS percentile_50
, MAX(CASE WHEN percentile <=60 THEN pctl_var END) AS percentile_60
, MAX(CASE WHEN percentile <=70 THEN pctl_var END) AS percentile_70
, MAX(CASE WHEN percentile <=80 THEN pctl_var END) AS percentile_80
, MAX(CASE WHEN percentile <=90 THEN pctl_var END) AS percentile_90
, MAX(CASE WHEN percentile <=100 THEN pctl_var END) AS percentile_100
from
(
select
,col
,pctl_Var

from
table1 as a
) as b
group by 1