Postgresql - Simpler way to count occurrences of a boolean condition [duplicate]

Question

I am trying to count the total number of rows returned, additionally I want to count the number of occurrences of a boolean condition. For example:

SELECT COUNT(1), COUNT(field > 42)
FROM some_table
WHERE some_other_conditions

However the query above doesn't work because the boolean condition field > 42 evaluates to false, which is still counted. I can rewrite the query to this, which does work:

SELECT COUNT(1), COUNT(CASE WHEN field > 42 THEN true END)
FROM some_table
WHERE some_other_conditions

What I am doing here is leveraging the missing ELSE clause of the CASE to default to a NULL value - which isn't counted. Note: The queries above are just examples, the real query has additional fields on the select, joins, group by and order by clauses.

The COUNT(CASE ... syntax is rather convoluted, is there a more concise way to express the same concept?

Bergi · Accepted Answer · 2023-12-21 08:24:55Z

1

You can use SUM instead of COUNT:

SELECT COUNT(*), SUM((field > 42)::int)
FROM some_table
WHERE some_other_conditions

or more appropriately a FILTER:

SELECT COUNT(*), COUNT(*) FILTER (WHERE field > 42)
FROM some_table
WHERE some_other_conditions

answered Dec 21, 2023 at 8:24

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql - Simpler way to count occurrences of a boolean condition [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related