Postgresql – Simpler way to count occurrences of a boolean condition

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?

>Solution :

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