# How the PERCENTILE_DISC() function works in Mariadb?

he `PERCENTILE_DISC()`

function is a built-in function in Mariadb that returns the value of a given percentile within a group of values.

The `PERCENTILE_DISC()`

function is a built-in function in Mariadb that returns the value of a given percentile within a group of values. The function is useful for finding the median, quartiles, or any other percentile of a distribution. The function is also known as `PERCENTILE_DISCRETE()`

.

## Syntax

The syntax of the `PERCENTILE_DISC()`

function is as follows:

```
PERCENTILE_DISC(percentile) OVER (
PARTITION BY expr1, expr2, ...
ORDER BY expr3, expr4, ...
)
```

Where `percentile`

is a decimal value between 0 and 1, inclusive, that specifies the percentile to compute. `expr1`

, `expr2`

, … are the expressions that define the partition or the group of values, and `expr3`

, `expr4`

, … are the expressions that define the order or the ranking of the values within each partition. The function returns a decimal value that is the value of the given percentile.

## Examples

### Example 1: Calculating the median of students’ scores

The following example shows how to use the `PERCENTILE_DISC()`

function to calculate the median of students’ scores in a table called `students`

:

```
SELECT name, score, PERCENTILE_DISC(0.5) OVER (ORDER BY score) AS Median
FROM students;
```

The output is:

```
+------+-------+--------+
| name | score | Median |
+------+-------+--------+
| Bob | 50 | 70 |
| Alice| 60 | 70 |
| Eve | 70 | 70 |
| Dave | 80 | 80 |
| Carol| 90 | 80 |
+------+-------+--------+
```

The function returns the median of the students’ scores, which is 70. The median is the value that divides the distribution into two equal halves, or the 50th percentile. The function returns the discrete value that is closest to the given percentile, rather than using a linear interpolation method.

### Example 2: Calculating the quartiles of products’ sales

The following example shows how to use the `PERCENTILE_DISC()`

function to calculate the quartiles of products’ sales in a table called `products`

:

```
SELECT product, category, sales,
PERCENTILE_DISC(0.25) OVER (PARTITION BY category ORDER BY sales) AS Q1,
PERCENTILE_DISC(0.5) OVER (PARTITION BY category ORDER BY sales) AS Q2,
PERCENTILE_DISC(0.75) OVER (PARTITION BY category ORDER BY sales) AS Q3
FROM products;
```

The output is:

```
+---------+----------+-------+------+------+------+
| product | category | sales | Q1 | Q2 | Q3 |
+---------+----------+-------+------+------+------+
| A | Books | 100 | 100 | 200 | 300 |
| B | Books | 200 | 100 | 200 | 300 |
| C | Books | 300 | 100 | 200 | 300 |
| D | Books | 400 | 100 | 200 | 300 |
| E | Toys | 50 | 50 | 100 | 150 |
| F | Toys | 100 | 50 | 100 | 150 |
| G | Toys | 150 | 50 | 100 | 150 |
+---------+----------+-------+------+------+------+
```

The function returns the quartiles of the products’ sales, which are the values that divide the distribution into four equal parts, or the 25th, 50th, and 75th percentiles. The function returns the quartiles for each category separately, using the partition by clause. The function returns the discrete value that is closest to the given percentile, rather than using a linear interpolation method.

## Related Functions

There are some other functions in Mariadb that are related to the `PERCENTILE_DISC()`

function. They are:

`PERCENTILE_CONT()`

: This function returns the value of a given percentile within a group of values. The function is similar to the`PERCENTILE_DISC()`

function, but it uses a linear interpolation method to compute the percentile value when it is not an integer rank. The function is also known as`PERCENTILE_CONTINUOUS()`

.`PERCENT_RANK()`

: This function returns the relative rank of a row within a group of rows. The function is similar to the`PERCENTILE_DISC()`

function, but it returns the percentile of a given value, rather than the value of a given percentile. The function is also known as`PERCENTILE_RANK()`

.`MEDIAN()`

: This function returns the median of a set of values. The function is equivalent to the`PERCENTILE_DISC(0.5)`

function, but it does not require the over clause.

## Conclusion

The `PERCENTILE_DISC()`

function is a useful function in Mariadb that allows you to calculate the value of a given percentile within a group of values. The function is helpful for finding the median, quartiles, or any other percentile of a distribution. The function returns the discrete value that is closest to the given percentile, rather than using a linear interpolation method. You can also use other functions like `PERCENTILE_CONT()`

, `PERCENT_RANK()`

, and `MEDIAN()`

to manipulate percentiles in different ways. I hope this article helped you understand how the `PERCENTILE_DISC()`

function works in Mariadb.