Median in SQL

Example query

This example assumes: orders(id, region text, total_amount numeric). It is written in PostgreSQL syntax, and your table and column names will differ from these.

SELECT region,
       COUNT(*) AS orders,
       AVG(total_amount) AS mean_order_value,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total_amount) AS median_order_value
FROM orders
GROUP BY region
ORDER BY region;

How it works

• PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x) returns the value at the 50th percentile, interpolating between the two middle values when the count is even.
• It is an aggregate, so it works with GROUP BY and gives one median per region.
• Showing the mean beside the median tells you at a glance how skewed the data is.

MySQL, BigQuery, SQL Server and SQLite differences

• PERCENTILE_CONT is available in PostgreSQL, SQL Server (as a window function with OVER) and Oracle. MySQL and SQLite have no built-in median; you number the rows and pick the middle one.
• In BigQuery use PERCENTILE_CONT(total_amount, 0.5) OVER (PARTITION BY region), or APPROX_QUANTILES for large tables.

Common mistakes

• PERCENTILE_CONT interpolates; PERCENTILE_DISC returns an actual value from the data. They differ when the count is even.
• Nulls are ignored, so the median is of the non-null rows only.
• A median of a small group (a handful of rows) is a weak signal; show the row count beside it.

Ask for it in plain English

Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the query matches the definition your business uses.

• What is the median order value by region?
• Compare the average and median ticket resolution time.