PostgreSQL: How to Count True and False Values

You can use the following methods in PostgreSQL to count the number of True and False values in a specific column of a table:

Method 1: Count Number of True Values in Column

SELECT COUNT(*) FILTER (WHERE all_star)
FROM athletes;

This particular example counts the total number of True values in the all_star column of the athletes table.

Method 2: Count Number of False Values in Column

SELECT COUNT(*) FILTER (WHERE NOT all_star)
FROM athletes;

This particular example counts the total number of False values in the all_star column of the athletes table.

The following example shows how to use each of these methods in practice.

Example: How to Count True and False Values 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,
  all_star BOOLEAN
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'Mavs', true);
INSERT INTO athletes VALUES (0002, 'Mavs', true);
INSERT INTO athletes VALUES (0003, 'Cavs', false);
INSERT INTO athletes VALUES (0004, 'Heat', true);
INSERT INTO athletes VALUES (0005, 'Mavs', false);
INSERT INTO athletes VALUES (0006, 'Thunder', true);
INSERT INTO athletes VALUES (0007, 'Cavs', false);
INSERT INTO athletes VALUES (0008, 'Lakers', false);
INSERT INTO athletes VALUES (0009, 'Celtics', false);
INSERT INTO athletes VALUES (0010, 'Lakers', false);

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

 id |  team   | all_star 
----+---------+----------
  1 | Mavs    | t
  2 | Mavs    | t
  3 | Cavs    | f
  4 | Heat    | t
  5 | Mavs    | f
  6 | Thunder | t
  7 | Cavs    | f
  8 | Lakers  | f
  9 | Celtics | f
 10 | Lakers  | f

The all_star column contains the values True and False to indicate whether or not each player is an all-star player.

We can use the following syntax to count the number of True values in the all_star column, i.e. the number of players who are an all-star:

SELECT COUNT(*) FILTER (WHERE all_star)
FROM athletes;

 count 
-------
     4

The output tells us that there are 4 total True values in the all_star column.

We can use similar syntax to count the number of False values in the all_star column, i.e. the number of players who are not an all-star:

SELECT COUNT(*) FILTER (WHERE NOT all_star)
FROM athletes;

 count 
-------
     6

The output tells us that there are 6 total False values in the all_star column.

Feel free to use similar syntax to count the number of True and False values in a Boolean column in your own table.

Additional Resources

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

PostgreSQL: How to Find Duplicate Records in a Table
PostgreSQL: Count Occurrences of Each Value in Column
PostgreSQL: Count Number of Occurrences of Character in String