PostgreSQL: Count Occurrences of Each Value in Column

Often in PostgreSQL you may want to count the number of occurrences of each value in a column.

You can use the following syntax to do so:

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

This particular example counts the number of occurrences of each value in the team column of the table named athletes.

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

Example: Count Occurrences of Each Value in Column in PostgreSQL

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

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

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'Mavs', 22, 5);
INSERT INTO athletes VALUES (0002, 'Hawks', 30, 12);
INSERT INTO athletes VALUES (0003, 'Kings', 12, 3);
INSERT INTO athletes VALUES (0004, 'Kings', 25, 9);
INSERT INTO athletes VALUES (0005, 'Celtics', 10, 5);
INSERT INTO athletes VALUES (0006, 'Nuggets', 14, 13);
INSERT INTO athletes VALUES (0007, 'Nuggets', 20, 8);
INSERT INTO athletes VALUES (0008, 'Nuggets', 26, 3);

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

 athleteid |  team   | points | assists 
-----------+---------+--------+---------
         1 | Mavs    |     22 |       5
         2 | Hawks   |     30 |      12
         3 | Kings   |     12 |       3
         4 | Kings   |     25 |       9
         5 | Celtics |     10 |       5
         6 | Nuggets |     14 |      13
         7 | Nuggets |     20 |       8
         8 | Nuggets |     26 |       3

Suppose that we would like to count the number of occurrences of each string in the team column.

We can use the following syntax to do so:

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

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

Notice that the resulting table contains a column that shows each team name along with a column named count that shows the number of occurrences of each team name.

For example, we can see:

• The team name Nuggets occurs 3 times.
• The team name Kings occurs 2 times.
• The team name Mavs occurs 1 time.

And so on.

Feel free to use similar syntax to count the number of occurrences of each value in a column of your own table.

Additional Resources

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

PostgreSQL: Extract Substring Before Specific Character
PostgreSQL: Extract Substring After Specific Character
PostgreSQL: How to Extract Substring Between Two Characters