# PERCENTILE\_DISC

In SingleStore, percentile functions are available as window functions and aggregate functions.

There are two percentile functions: [PERCENTILE\_CONT and MEDIAN](https://docs.singlestore.com/db/v9.1/reference/sql-reference/window-functions/percentile-cont-and-median.md) and `PERCENTILE_DISC`.

`PERCENTILE_DISC()` is an inverse distribution function that assumes a discrete distribution between values of the expression in the sort specification. It returns the first element whose sort position among all non-null rows is greater than or equal to the percentile. For an overview of window functions in SingleStore, see [Window Functions Guide](https://docs.singlestore.com/db/v9.1/developer-resources/functional-extensions/working-with-window-functions.md).

## Syntax

```
PERCENTILE_DISC(percentile)
WITHIN GROUP (ORDER BY (col | expr))
[OVER (
    [PARTITION BY (col | expr), ...]
    [frame_clause]
)]

```

or equivalently

```
PERCENTILE_DISC(percentile)
OVER (
        ORDER BY (col | expr)
        [PARTITION BY {col | expr}, ...]
        [frame_clause]
)

```

## Arguments

**percentile**

A numeric value between 0 and 1.

**col**

A column of any column data type.

**expr**

An expression that evaluates to any column data type.

## Remarks

`PERCENTILE_DISC()` can be used as a window function as well as an aggregate function.

The `OVER()` clause defines how to partition the input set. If no `PARTITION BY` clause is specified, the entire input set is treated as a single partition.

Only one column or expression is allowed in the `ORDER BY` clause, and this column is the one for which the percentile is calculated.

## Examples

**Example 1**

The following example demonstrates the use of `PERCENTILE_DISC` as an aggregate function.

```sql
CREATE TABLE perdisc_sets (a INT, b INT, c INT);

INSERT INTO perdisc_sets VALUES (1,1,2),(2,2,3),(3,1,2),(4,2,4),(5,1,1),(2,1,1),(null,null,3);

```

```sql
SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY a) FROM perdisc_sets;


```

```output

+------------------------------------------------+
| PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY a) |
+------------------------------------------------+
|                                              2 |
+------------------------------------------------+

```

```sql
SELECT c, PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY a) AS "PERCENTILE" FROM perdisc_sets GROUP BY c ORDER BY c;


```

```output

+------+------------+
| c    | PERCENTILE |
+------+------------+
|    1 |          2 |
|    2 |          1 |
|    3 |          2 |
|    4 |          4 |
+------+------------+

```

```sql
SELECT PERCENTILE_DISC(0.25)
WITHIN GROUP (ORDER BY a) AS "PERCENTILE"
FROM perdisc_sets;


```

```output

+------------+
| PERCENTILE |
+------------+
|          2 |
+------------+

```

The percentile of a data set may change depending on how it is ordered.

```sql
SELECT PERCENTILE_DISC(0.25)
WITHIN GROUP (ORDER BY a DESC) AS "PERCENTILE"
FROM perdisc_sets;


```

```output

+------------+
| PERCENTILE |
+------------+
|          4 |
+------------+

```

Multiple `PERCENTILE_DISC` functions can be used in a single query, and each function must have its own `WITHIN GROUP` clause.

```sql
SELECT PERCENTILE_DISC(0.25)
       WITHIN GROUP (ORDER BY a) AS "PERCENTILE a",
       PERCENTILE_DISC(0.25)
       WITHIN GROUP (ORDER BY b) AS "PERCENTILE b"
FROM perdisc_sets;


```

```output

+--------------+--------------+
| PERCENTILE a | PERCENTILE b |
+--------------+--------------+
|            2 |            1 |
+--------------+--------------+

```

**Example 2**

The following example creates a table containing accounts, the account type, and the current balance of each. The resulting window frame shows the discrete median of the balance for each account type.

```sql
CREATE TABLE perdisc_accounts(account_id INT, category VARCHAR(30), balance DECIMAL(18,2));

```

```sql
INSERT INTO perdisc_accounts VALUES
    (1, "Checking", 100.01), (2, "Savings", 1000.00),
    (3, "Checking", 12345.67), (4, "Savings", 654321.00), (null, "Checking", 2.00),
    (5, "Savings", 250.00), (6, "CD", 1500.00), (7, "CD", 100.00), (8, "Checking", 80.80);

```

```sql
SELECT account_id, category, balance,
PERCENTILE_DISC(.5)
WITHIN GROUP (ORDER BY balance)
OVER (PARTITION BY category)
AS discrete_median
FROM perdisc_accounts;


```

```output

+------------+----------+-----------+-----------------+
| account_id | category | balance   | discrete_median |
+------------+----------+-----------+-----------------+
|          7 | CD       |    100.00 |          100.00 |
|          6 | CD       |   1500.00 |          100.00 |
|          5 | Savings  |    250.00 |         1000.00 |
|          2 | Savings  |   1000.00 |         1000.00 |
|          4 | Savings  | 654321.00 |         1000.00 |
|       NULL | Checking |      2.00 |           80.80 |
|          8 | Checking |     80.80 |           80.80 |
|          1 | Checking |    100.01 |           80.80 |
|          3 | Checking |  12345.67 |           80.80 |
+------------+----------+-----------+-----------------+

```

***

Modified at: February 16, 2023

Source: [/db/v9.1/reference/sql-reference/window-functions/percentile-disc/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/window-functions/percentile-disc/)

(An index of the documentation is available at /llms.txt)