PostgreSQL: How to Calculate Mode by Group

Often in PostgreSQL you may want to calculate the statistical mode (the most common value) of one column, grouped by another column.

You can use the following syntax to do so:

SELECT team,
       mode() WITHIN GROUP (ORDER BY points)
FROM athletes
GROUP BY team;

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

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

Example: How to Calculate Mode 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,
  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', 30);
INSERT INTO athletes VALUES (0004, 'Mavs', 9);
INSERT INTO athletes VALUES (0005, 'Mavs', 30);
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 |     30
  4 | Mavs |      9
  5 | Mavs |     30
  6 | Heat |     16
  7 | Heat |     40
  8 | Heat |     18
  9 | Heat |     16
 10 | Heat |     33

Suppose that we would like to calculate the mode of the points column, grouped by the values in the team column.

We can use the following syntax to do so:

SELECT team,
       mode() WITHIN GROUP (ORDER BY points)
FROM athletes
GROUP BY team;

 team | mode 
------+------
 Heat |   16
 Mavs |   30

From the resulting table we can see the following:

• The mode points value for the Heat is 16.
• The mode points value for the Mavs is 30.

Feel free to use similar syntax to calculate the mode 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