SQL bool_and, bool_or, and FILTER

count and sum answer "how many" and "how much". Some questions about a group are yes or no instead: is every customer in this state active? Did anyone here join in 2024? Two aggregates answer them, each with a true or false per group:

• bool_and(condition) is true when the condition is true for every row in the group.
• bool_or(condition) is true when it is true for at least one row.

select state,
       count(*) as customers,
       bool_and(is_active) as all_active,
       bool_or(signup_date >= '2024-01-01') as any_joined_2024
from customers
group by state
order by state

The condition can be a boolean column like is_active or any comparison, like the signup date above. Pass anything else and DuckDB refuses: bool_and(1) fails with No function matches the given name and argument types 'bool_and(INTEGER)'.

Counting the rows that match

When the answer should be a number rather than yes or no, add filter (where ...) after an aggregate. It hands that aggregate only the rows where the condition is true. DuckDB also has count_if as a shorthand:

select state,
       count(*) as customers,
       count(*) filter (where is_active) as active,
       count_if(not is_active) as inactive
from customers
group by state
order by state

Prefer count(*) filter (where ...). PostgreSQL and SQLite run it too, and it returns 0 when nothing matches. count_if is DuckDB only, and when every condition it sees is NULL it returns NULL instead of 0. You may also see sum(is_active). DuckDB counts each true as 1, but PostgreSQL refuses with function sum(boolean) does not exist.

Missing values are skipped

Like sum and avg, bool_and and bool_or ignore NULL. Two orders, one shipped and one with no answer, are "all shipped":

select bool_and(shipped) as all_shipped,
       bool_and(shipped is true) as all_known_shipped,
       count(*) filter (where shipped) as shipped,
       count(*) as orders
from (values (true), (null)) as orders(shipped)

bool_and(shipped) really asks whether every known value is true. If a missing value should count as no, say so: shipped is true turns NULL into false, as in Lesson 2.8 · True, false, and unknown. A group where every value is NULL gets NULL from both, not true or false.

Keeping groups with HAVING

A boolean aggregate is already a condition, so it fits straight into HAVING. These are the states where every customer is active:

select state, count(*) as customers
from customers
group by state
having bool_and(is_active)
order by state

In other databases

• PostgreSQL has bool_and, bool_or, and FILTER, plus every, the SQL standard's name for bool_and. DuckDB has no every.
• MySQL and SQLite store booleans as 1 and 0, so min(flag) works as "every", max(flag) as "any", and sum(flag) counts. SQLite also has FILTER; MySQL doesn't.
• SQL Server has none of them, and its bit columns can't go into sum, min, or max: Operand data type bit is invalid for sum operator. Turn the condition into 1 or 0 with a CASE expression first, then use min, max, or sum.