Often in PostgreSQL you may want to use a COUNT DISTINCT statement with a CASE WHEN statement to count the number of distinct rows that meet some condition.
You can use the following syntax to do so:
SELECT COUNT(DISTINCT CASE WHEN team='Mavs' THEN points END) AS mavs_points
FROM athletes;
This particular example counts the number of distinct values in the points column where the corresponding value in the team column is equal to the string ‘Mavs’.
The count is then saved to a new column named mavs_points.
The following example shows how to use this syntax in practice.
Example: Use COUNT DISTINCT with CASE WHEN 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, 'Mavs', 31, 14);
INSERT INTO athletes VALUES (0006, 'Mavs', 16, 3);
INSERT INTO athletes VALUES (0007, 'Celtics', 10, 5);
INSERT INTO athletes VALUES (0008, 'Celtics', 14, 13);
INSERT INTO athletes VALUES (0009, 'Nuggets', 20, 8);
INSERT INTO athletes VALUES (0010, 'Hawks', 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 | Mavs | 31 | 14
6 | Mavs | 16 | 3
7 | Celtics | 10 | 5
8 | Celtics | 14 | 13
9 | Nuggets | 20 | 8
10 | Hawks | 26 | 3
Suppose that we would like to count the number of distinct values in the points column only for the rows where the team column is equal to Mavs.
We can use the following syntax to do so:
SELECT COUNT(DISTINCT CASE WHEN team='Mavs' THEN points END) AS mavs_points
FROM athletes;
mavs_points
-------------
3
From the output we can see that there are 3 distinct values in the points column for the rows where the team column is equal to Mavs.
Note that you can also use the CASE WHEN statement multiple times to count the number of distinct points values for multiple teams.
For example, we can use the following syntax to do so:
SELECT COUNT(DISTINCT CASE WHEN team='Mavs' THEN points END) AS mavs_points,
COUNT(DISTINCT CASE WHEN team='Kings' THEN points END) AS king_points
FROM athletes;
mavs_points | king_points
-------------+-------------
3 | 2
The output displays the number of unique values in the points column for both the Mavs and the Kings.
Feel free to use similar syntax to count the number of distinct values using a CASE WHEN statement 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
