SQL - PostgreSQL - Calculating the Median

Source: sql-snippets/median.md at main · count/sql-snippets (github.com)

View an interactive version of this snippet here.

Description

Postgres does not have an explicit MEDIAN function. The following snippet will show you how to calculate the median of any column.

Snippet ✂️

Here is a median function with a sample dataset:

WITH data AS (
    SELECT CASE
        WHEN RANDOM() <= 0.25 THEN 'apple'
        WHEN RANDOM() <= 0.5 THEN 'banana'
        WHEN RANDOM() <= 0.75 THEN 'pear'
      ELSE 'orange'
    END AS fruit,
    RANDOM()::DOUBLE PRECISION AS weight
    FROM generate_series(1,10)
)

SELECT
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY weight) AS median
FROM
  data

Output:

median
0.1358146

---

Appendix: Links

• Code
• SQL
• Databases
• PostgreSQL
• Development

Backlinks:

list from [[SQL - PostgreSQL - Calculating the Median]] AND -"Changelog"