Often in PostgreSQL you may want to select all rows that have a count greater than some number in a specific column.
You can use the following syntax to do so:
SELECT *
FROM athletes
WHERE team IN (SELECT team
FROM athletes
GROUP BY team HAVING COUNT(*) > 1);
This particular example will select all rows from the table named athletes where the value in the team column occurs more than 1 time.
The following example shows how to use this syntax in practice.
Example: Select Rows Having Count Greater Than a Number 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 only select the rows that have a team name that occurs more than once in the team column.
We can use the following syntax to do so:
SELECT *
FROM athletes
WHERE team IN (SELECT team
FROM athletes
GROUP BY team HAVING COUNT(*) > 1);
athleteid | team | points | assists
-----------+---------+--------+---------
3 | Kings | 12 | 3
4 | Kings | 25 | 9
6 | Nuggets | 14 | 13
7 | Nuggets | 20 | 8
8 | Nuggets | 26 | 3
The resulting table contains only the rows for players whose team name occurs more than once in the team column.
Specifically, we can see that only the players on the Kings and Nuggets are shown in the resulting table because these are the only team names that occur more than once in the team column of the original table.
Feel free to replace team with the name of the column that you’d like to use for counting in your own table.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: Count Occurrences of Each Value in Column
PostgreSQL: Count Number of Occurrences of Character in String
PostgreSQL: How to Extract Numbers from a String
