PostgreSQL: How to Calculate Median by Group

Often in PostgreSQL you may want to calculate the median value of one column, grouped by another column.

You can use the following syntax to do so:

SELECT team,
       PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY points) AS median_points
FROM athletes
GROUP BY team;

This particular example will calculate the median value of the points column, grouped by the team column in the table named athletes.

Note that we use the PERCENTILE_CONT function, which calculates the percentile value of a column. By providing a value of 0.5, the function calculates the 50th percentile, which is equivalent to the median value.

The following example shows how to use this syntax in practice.

Example: How to Calculate Median by Group in PostgreSQL

Suppose that we create the following table named athletes that contains information about points scored by various basketball players:

-- create table named 'athletes'
CREATE TABLE athletes (
  id INTEGER PRIMARY KEY,
  team TEXT,
  points INTEGER
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'Mavs', 22);
INSERT INTO athletes VALUES (0002, 'Mavs', 30);
INSERT INTO athletes VALUES (0003, 'Mavs', 12);
INSERT INTO athletes VALUES (0004, 'Mavs', 9);
INSERT INTO athletes VALUES (0005, 'Mavs', 14);
INSERT INTO athletes VALUES (0006, 'Heat', 16);
INSERT INTO athletes VALUES (0007, 'Heat', 40);
INSERT INTO athletes VALUES (0008, 'Heat', 18);
INSERT INTO athletes VALUES (0009, 'Heat', 16);
INSERT INTO athletes VALUES (0010, 'Heat', 33);

-- view all rows in table
SELECT * FROM athletes;

 id | team | points 
----+------+--------
  1 | Mavs |     22
  2 | Mavs |     30
  3 | Mavs |     12
  4 | Mavs |      9
  5 | Mavs |     14
  6 | Heat |     16
  7 | Heat |     40
  8 | Heat |     18
  9 | Heat |     16
 10 | Heat |     33

Suppose that we would like to calculate the median number of points scored by players on each team.

We can use the following syntax to do so:

SELECT team,
       PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY points) AS median_points
FROM athletes
GROUP BY team;

 team | median_points 
------+---------------
 Heat |            18
 Mavs |            14

From the resulting table we can see the following:

• The median points scored by players on the Heat is 18.
• The median points scored by players on the Mavs is 14.

Note that the median represents the “middle” number in a dataset, when all of the values are arranged from smallest to largest.

We can verify that the median calculated here is correct by manually identifying the median value for each team:

• Heat: 16, 16, 18, 33, 40
• Mavs: 9, 12, 14, 22, 30

This confirms that the median values are correct in the output table.

Feel free to use similar syntax to calculate the median value by group in your own table.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Select Top N Percent of Records
PostgreSQL: How to Calculate a Moving Average
PostgreSQL: How to Calculate Average Across Multiple Columns