Often in PostgreSQL you may want to calculate the mean, median and mode of a particular column in a table.
You can use the following syntax to do so:
SELECT
AVG(points) AS average_points,
PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY points) AS median_points,
MODE() WITHIN GROUP(ORDER BY points) AS mode_points
FROM athletes;
This particular example calculates the mean, median and mode of the values in the points column of the athletes table.
The following example shows how to use this syntax in practice.
Example: How to Calculate Mean, Median and Mode 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', 30);
INSERT INTO athletes VALUES (0009, 'Heat', 16);
INSERT INTO athletes VALUES (0010, 'Heat', 30);
-- 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 | 30
9 | Heat | 16
10 | Heat | 30
Suppose that we would like to calculate the mean, median and mode of values in the points column.
We can use the following syntax to do so:
SELECT
AVG(points) AS average_points,
PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY points) AS median_points,
MODE() WITHIN GROUP(ORDER BY points) AS mode_points
FROM athletes;
average_points | median_points | mode_points
---------------------+---------------+-------------
21.9000000000000000 | 19 | 30
From the resulting table we can see the following:
The mean number of points scored is 21.9. This represents the average number of points scored.
The median number of points scored is 19. If we arranged all of the values in the points column from smallest to largest, this would represent the “middle” number.
The mode number of points scored is 30. This represents the most common value in the points column.
Feel free to use similar syntax to calculate the mean, median and mode of values in a column of your own table in PostgreSQL.
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
