Postgresql - sql - count of `true` values

sql

prompt-engineering

best-practices

join

byAlex Kataev·Jan 17, 2025

Here's the fast lane to count true booleans in a PostgreSQL column:

SELECT COUNT(*) AS true_count FROM your_table WHERE your_column;

A simple, yet effective approach for counting rows where your_column returns true, bypassing any need for conversions or CASE statements.

Smart solutions to add nuance

Some scenarios require you to cater to all possibilities: true, false, and even null. PostgreSQL is flexible enough to deal with such cases.

Counting nulls as false

Ensure that null entries are included in the count by utilizing COALESCE:

// Mother always said, "Null values are as good as false!" 😆
SELECT COUNT(*) AS total_rows, SUM(COALESCE(your_column, false)::int) AS true_count FROM your_table;

Knowing your true from false

Count true and false values separately in one query, thanks to our friend, the FILTER clause:

SELECT
  COUNT(*) AS total_rows,
  COUNT(*) FILTER (WHERE your_column) AS true_count,
  COUNT(*) FILTER (WHERE NOT your_column) AS false_count,
  COUNT(*) FILTER (WHERE your_column IS NULL) AS nulls
FROM your_table;

Embracing `CASE` for precision

If you want to be ultra precise, wrap a CASE statement around your counts:

// True or false, you can count on me! 😄
SELECT
  COUNT(CASE WHEN your_column THEN 1 END) AS true_count,
  COUNT(CASE WHEN NOT your_column THEN 1 END) AS false_count
FROM your_table;

Advanced tacticts: Counting made complex

As your SQL grows, so does the need for agile solutions. It's a good thing PostgreSQL is up for the challenge!

Complex expressions and null handling

You can form complex column expressions and still keep null handling intact:

// Though counting true values, never nullifying the importance of nulls!  🙃
SELECT COUNT(your_column OR NULL) AS true_including_nulls FROM your_table;

Staying ingenious with aggregates

You don't need subqueries to aggregate different conditions, check this out:

// Because who needs extra subqueries when counting, right?! 😉
SELECT
  COUNT(*) AS total_rows,
  SUM((your_column AND another_condition)::int) AS conditional_true_count
FROM your_table;

Conditional functions for precision counting

PostgreSQL’s documentation is your golden ticket to precision in aggregates:

// Even null values can't escape from our count! 😅
SELECT
  SUM(COALESCE(nullif(your_column, false), false)::int) AS true_count_also_counting_nulls_as_false
FROM your_table;

Feel free to get creative with nullif to count either true or false:

// True or false, I'll count you each time! 😁
SELECT
  COUNT(nullif(your_column, false)) AS true_count,
  COUNT(nullif(your_column, true)) AS false_count,
FROM your_table;

Maintaining count accuracy with multiple conditions

Using conditional expressions maintains count accuracy:

// Complex conditions? More like funions for counting, amirite?! 😎
SELECT
  SUM(CASE WHEN your_column AND complex_condition THEN 1 ELSE 0 END) AS complex_true_count
FROM your_table;

Mastering the nuances of Postgres counting

Get the hang of PostgreSQL's distinctive counting capabilities for crafting better queries.

Harnessing type conversion for precision

Tapping into the power of type conversion (::int) provides a straightforward way to count true values:

// CASTing a wider net to catch all the trues in our column! 😊
SELECT COUNT(your_column::int) AS true_count FROM your_table;

Validating aggregate counts

These methods are tried and tested. For example, COALESCE and CASE have been confirmed to return accurate results:

// Because validation is the key to accurate counting! 🗝️
SELECT
  COUNT(CASE WHEN your_column IS NOT NULL AND your_column THEN 1 END) AS validated_true_count
FROM your_table;