PERCENTILE

PERCENTILE computes a specified percentile of the values of an attribute for all records in the group.

The syntax of the PERCENTILE function is:

PERCENTILE(<attribute>, <numeric_literal>)

where:

attribute is a single-assign, numeric attribute. The EQL data type for the attribute must be either mdex:long or mdex:double.
numeric_literal is the percentile to compute. The value must range between 0 (greater than or equal to 0) and 100 (less than or equal to 100). You can specify the value as an integer (such as 50) or a double (such as 50.5). For example, 75 will compute the 75th percentile of an expression. Note that a percentile of 50 is identical to the median.

Note that if the percentile falls between two values, then EQL computes a weighted average. As an example, suppose there are only two values, 10 and 20. If you ask for the 20th percentile, then the result will be 12, because 12 is 20% of the way from 10 to 20.

PERCENTILE ignores rows in which its first argument is NULL. If the first argument is NULL for all rows in a group, PERCENTILE returns NULL for that group.

PERCENTILE examples

In both examples, SalesAmount is a single-assign double attribute.

This example returns the 90th percentile of the SalesAmount values within the group:

RETURN Results AS
SELECT PERCENTILE(SalesAmount, 90) AS x90
FROM SalesState
GROUP

The result for this example might be:

x90
----------
| 571.18 |
----------

This example returns the 25th, 50th, and 75th percentiles of the SalesAmount values within the group:

RETURN Results AS
SELECT 
  PERCENTILE(SalesAmount, 25) AS x25,
  PERCENTILE(SalesAmount, 50) AS x50,
  PERCENTILE(SalesAmount, 75) AS x75
GROUP

The result for this example might be:

x25         x50     x75
-----------------------------
| 180.225 | 236.5 | 445.675 |
-----------------------------