How to Order by Count in PostgreSQL (With Example)

Often in PostgreSQL you may want to order the rows of a table based on the count of values in a particular column.

You can use the following syntax to do so:

SELECT team, COUNT(team)
FROM athletes
GROUP BY team
ORDER BY COUNT(team);

This particular example orders the rows of the athletes table based on the count of values in the team column, grouped by the values in the team column.

Note: The ORDER BY statement orders rows in ascending order by default, but you can specify DESC to instead order the rows by count in descending order.

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

Example: How to Order by Count 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, 'Hawks', 30);
INSERT INTO athletes VALUES (0003, 'Kings', 12);
INSERT INTO athletes VALUES (0004, 'Kings', 25);
INSERT INTO athletes VALUES (0005, 'Mavs', 31);
INSERT INTO athletes VALUES (0006, 'Mavs', 16);
INSERT INTO athletes VALUES (0007, 'Celtics', 40);
INSERT INTO athletes VALUES (0008, 'Celtics', 14);
INSERT INTO athletes VALUES (0009, 'Nuggets', 19);
INSERT INTO athletes VALUES (0010, 'Hawks', 26);

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

 id |  team   | points 
----+---------+--------
  1 | Mavs    |     22
  2 | Hawks   |     30
  3 | Kings   |     12
  4 | Kings   |     25
  5 | Mavs    |     31
  6 | Mavs    |     16
  7 | Celtics |     40
  8 | Celtics |     14
  9 | Nuggets |     19
 10 | Hawks   |     26

We can use the following syntax to order the rows of the table based on the count of values in the team column, grouped by the team column:

SELECT team, COUNT(team)
FROM athletes
GROUP BY team
ORDER BY COUNT(team);

  team   | count 
---------+-------
 Nuggets |     1
 Kings   |     2
 Hawks   |     2
 Celtics |     2
 Mavs    |     3

The resulting table shows the count of occurrences of each value in the team column, sorted in ascending order.

We can specify DESC after the ORDER BY clause to instead sort by count in descending order:

SELECT team, COUNT(team)
FROM athletes
GROUP BY team
ORDER BY COUNT(team) DESC;

  team   | count 
---------+-------
 Mavs    |     3
 Kings   |     2
 Hawks   |     2
 Celtics |     2
 Nuggets |     1

The resulting table shows the count of occurrences of each value in the team column, sorted in descending order.

Additional Resources

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

PostgreSQL: ORDER BY Multiple Values in Specific Order
PostgreSQL: How to Use ORDER BY with NULL Values Last
PostgreSQL: How to Count Occurrences of Each Value in Column