Often in PostgreSQL you may want to use COUNTIF logic to only count rows in a table that meet one or more conditions.
You can use the following methods to do so:
Method 1: Count Rows that Meet One Condition
SELECT COUNT(*)
FROM athletes
WHERE team = 'Nuggets';
This particular example counts the number of rows in the athletes table where the value in the team column is equal to Nuggets.
Method 2: Count Rows that Meet Multiple Conditions
SELECT COUNT(*)
FROM athletes
WHERE team = 'Nuggets' AND points > 15;
This particular example counts the number of rows in the athletes table where the value in the team column is equal to Nuggets and the value in the points column is greater than 15.
The following example shows how to use each of these methods in practice.
Example: How to Use COUNTIF 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
We can use the following syntax to counts the number of rows in the table where the value in the team column is equal to Nuggets:
SELECT COUNT(*)
FROM athletes
WHERE team = 'Nuggets';
count
-------
3
From the output we can see that there are 3 rows that meet this condition.
We can also use the following syntax to count the number of rows in the table where the value in the team column is equal to Nuggets and the value in the points column is greater than 15:
SELECT COUNT(*)
FROM athletes
WHERE team = 'Nuggets' AND points > 15;
count
-------
2
From the output we can see that there are 2 rows that meet both of these conditions.
Feel free to use similar syntax to perform a COUNTIF 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 Count Values by Range
