# Median and percentiles per group

Shape / table

A variant of [Summarise by group](https://meridian.online/operations/summarise-by-group).

Compute the median, a quantile or several percentiles of each group, exactly or approximately.

Page: <https://meridian.online/operations/summarise-by-group/percentiles-per-group>

## Status

Runs as a SQL step.

arcform carries it as a `sql:` step today.

## The DuckDB step

median and percentiles of each group, exact and approximate

```sql
-- amount is cast to DOUBLE so a percentile between two values is not rounded to the column's two decimals
CREATE TEMP TABLE result AS
SELECT product,
       median(amount::DOUBLE) AS median,
       round(quantile_cont(amount::DOUBLE, 0.25), 4) AS q1,
       round(percentile_cont(0.75) WITHIN GROUP (ORDER BY amount::DOUBLE), 4) AS q3,
       percentile_disc(0.5) WITHIN GROUP (ORDER BY amount) AS median_disc,
       quantile_cont(amount::DOUBLE, [0.25, 0.5, 0.75]) AS quartiles,
       approx_quantile(amount::DOUBLE, 0.5) AS median_approx
FROM orders
GROUP BY product;
SELECT product, median, q1, q3, median_disc, median_approx FROM result ORDER BY product;
SELECT CASE WHEN ((SELECT list((product, median, q1, q3, median_disc) ORDER BY product) FROM result) = [('gadget', 45.25, 30.125, 62.875, 45.25), ('gizmo', 159.995, 129.9925, 189.9975, 99.99), ('widget', 405.0, 255.0, 510.0, 300.0)] AND (SELECT bool_and(len(quartiles) = 3 AND quartiles[2] = median) FROM result) AND (SELECT list((product, median_approx) ORDER BY product) FROM result) = [('gadget', 45.25), ('gizmo', 159.995), ('widget', 405.0)] AND (SELECT bool_and(median_approx BETWEEN lo AND hi) FROM result JOIN (SELECT product, min(amount) AS lo, max(amount) AS hi FROM orders GROUP BY product) USING (product))) THEN 'as expected' ELSE error('the exact median, quartiles and discrete median per product were wrong, or the approximate median was not the pinned value inside its group range') END AS verdict;
```

The statement builds its own data.

It needs nothing installed beyond DuckDB itself.

## What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

- Apache DataFusion: [`WITHIN GROUP (ORDER BY ...)`](https://datafusion.apache.org/user-guide/sql/aggregate_functions.html) (read 2026-09-27)
- Apache DataFusion: [`approx_median`](https://datafusion.apache.org/user-guide/sql/aggregate_functions.html) (read 2026-09-27)
- Apache DataFusion: [`approx_percentile_cont`](https://datafusion.apache.org/user-guide/sql/aggregate_functions.html) (read 2026-09-27)
- Apache DataFusion: [`approx_percentile_cont_with_weight`](https://datafusion.apache.org/user-guide/sql/aggregate_functions.html) (read 2026-09-27)
- Apache DataFusion: [`median`](https://datafusion.apache.org/user-guide/sql/aggregate_functions.html) (read 2026-09-27)
- Apache DataFusion: [`percentile_cont`](https://datafusion.apache.org/user-guide/sql/aggregate_functions.html) (read 2026-09-27)
- Duckle: [`Approx Quantile`](https://github.com/slothflowlabs/duckle/blob/f85f8960edfb6efbf4cb89d58a7b32e16df0e867/website/docs/components.html) (read 2026-09-25)
- EasyMorph: [`Statistical aggregation`](https://help.easymorph.com/doku.php?id=transformations:statisticalaggregation) (read 2026-09-27)
- Ibis: [`ApproxMedian`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/reductions.py) (read 2026-09-27)
- Ibis: [`ApproxMultiQuantile`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/reductions.py) (read 2026-09-27)
- Ibis: [`ApproxQuantile`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/reductions.py) (read 2026-09-27)
- Ibis: [`Median`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/reductions.py) (read 2026-09-27)
- Ibis: [`MultiQuantile`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/reductions.py) (read 2026-09-27)
- Ibis: [`Quantile`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/reductions.py) (read 2026-09-27)
- Polars: [`GroupBy.median`](https://docs.pola.rs/api/python/stable/reference/dataframe/group_by.html) (read 2026-09-27)
- Polars: [`GroupBy.quantile`](https://docs.pola.rs/api/python/stable/reference/dataframe/group_by.html) (read 2026-09-27)
- Trifacta: [`APPROXIMATEMEDIAN Function`](https://help.alteryx.com/aac/en/trifacta-classic/wrangle-language/aggregate-functions.html) (read 2026-09-27)
- Trifacta: [`APPROXIMATEPERCENTILE Function`](https://help.alteryx.com/aac/en/trifacta-classic/wrangle-language/aggregate-functions.html) (read 2026-09-27)
- Trifacta: [`APPROXIMATEQUARTILE Function`](https://help.alteryx.com/aac/en/trifacta-classic/wrangle-language/aggregate-functions.html) (read 2026-09-27)
- Trifacta: [`MEDIAN Function`](https://help.alteryx.com/aac/en/trifacta-classic/wrangle-language/aggregate-functions.html) (read 2026-09-27)
- Trifacta: [`PERCENTILE Function`](https://help.alteryx.com/aac/en/trifacta-classic/wrangle-language/aggregate-functions.html) (read 2026-09-27)
- Trifacta: [`QUARTILE Function`](https://help.alteryx.com/aac/en/trifacta-classic/wrangle-language/aggregate-functions.html) (read 2026-09-27)