PostgreSQL: How to Count Distinct Over Multiple Columns

Often in PostgreSQL you may want to count the distinct number of values over multiple columns.

You can use the following syntax to do so:

SELECT COUNT(DISTINCT (team, position))
FROM athletes;

This particular example counts the number of distinct values over the team and position columns of the table named athletes.

Note that the inner DISTINCT statement first returns the distinct combinations of values over the team and position columns.

Then, the outer COUNT statement counts the number of rows returned.

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

Example: How to Count Distinct Over Multiple 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 PRIMARY KEY,
  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, 'Hawks', 'Guard', 29);
INSERT INTO athletes VALUES (0005, 'Hawks', 'Forward', 14);
INSERT INTO athletes VALUES (0006, 'Hawks', 'Forward', 33);

-- 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 | Hawks | Guard    |     29
         5 | Hawks | Forward  |     14
         6 | Hawks | Forward  |     33

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

We can use the following syntax to do so:

SELECT COUNT(DISTINCT (team, position))
FROM athletes;

 count 
-------
     4

From the output we can see that there are 4 distinct combinations of values across the team and position columns in the table.

If we leave out the COUNT statement, we can view these 4 distinct combinations:

SELECT DISTINCT team, position
FROM athletes;

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

Feel free to use similar syntax to count the number of distinct values across multiple columns in your own table.

Additional Resources

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

PostgreSQL: How to Count Occurrences of Each Value in Column
PostgreSQL: How to Count Number of Occurrences of Character in String
PostgreSQL: How to Count True and False Values