PostgreSQL: Count Unique Combinations of Two Columns

Often in PostgreSQL you may want to count the number of unique combinations of values across two columns.

You can use the following syntax to do so:

SELECT COUNT(*) FROM (
  SELECT DISTINCT team, position FROM athletes
) t

This particular example counts the number of unique combinations of values across the team and position columns of the athletes table.

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

Example: Count Unique Combinations of Two Columns 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,
  team TEXT,
  position TEXT,
  points INTEGER
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'Mavs', 'Guard', 15);
INSERT INTO athletes VALUES (0002, 'Mavs', 'Guard', 12);
INSERT INTO athletes VALUES (0003, 'Mavs', 'Forward', 23);
INSERT INTO athletes VALUES (0004, 'Mavs', 'Forward', 40);
INSERT INTO athletes VALUES (0005, 'Hawks', 'Guard', 29);
INSERT INTO athletes VALUES (0006, 'Hawks', 'Forward', 14);
INSERT INTO athletes VALUES (0007, 'Hawks', 'Forward', 33);
INSERT INTO athletes VALUES (0008, 'Hawks', 'Forward', 12);

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

 athleteid | team  | position | points 
-----------+-------+----------+--------
         1 | Mavs  | Guard    |     15
         2 | Mavs  | Guard    |     12
         3 | Mavs  | Forward  |     23
         4 | Mavs  | Forward  |     40
         5 | Hawks | Guard    |     29
         6 | Hawks | Forward  |     14
         7 | Hawks | Forward  |     33
         8 | Hawks | Forward  |     12

Suppose that we would like to count the number of unique combinations across the team and position columns of the table.

We can use the following syntax to do so:

SELECT COUNT(*) FROM (
  SELECT DISTINCT team, position FROM athletes
) t

 count 
-------
     4

From the output we can see that there are 4 unique combinations of values across the team and positions columns.

If we would like to see each of these unique combination, we can could use the following query:

SELECT DISTINCT team, position FROM athletes;

 team  | position 
-------+----------
 Hawks | Guard
 Mavs  | Forward
 Mavs  | Guard
 Hawks | Forward

The resulting table shows each of the unique combinations of values across these two columns.

You can use similar syntax to count the number of unique combinations across two columns in your own table in PostgreSQL.

Additional Resources

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

PostgreSQL: How to Count Duplicate Rows in Table
PostgreSQL: How to Count Null Values in Column
PostgreSQL: How to Use COUNTIF