AggregatePostgreSQL only

BOOL_AND

Returns true if a condition is true for all rows.

Syntax

BOOL_AND(<condition>)
EVERY(<condition>)

The “all of them?” aggregate function. Together with GROUP BY it answers that for each group: does everyone in the department have a bonus. It skips NULL. EVERY is the same thing under its standard SQL name. Its mirror function is BOOL_OR: “is there at least one?”.

Data for the examples

employees

name	department	salary	bonus	phone
Emma	backend	1200	300	+44 7700 900123
Liam	backend	1000	NULL	NULL
Olivia	frontend	1350	150	+44 7700 900456
Noah	frontend	950	NULL	+44 7700 900789
Sophia	design	1200	200	NULL

These tables already exist in the sandbox — the examples below use them. Want to try this in your own database? Copy the SQL.

Does everyone earn over 900, and over 1000

SELECT BOOL_AND(salary > 900) AS everyone_above_900,

BOOL_AND(salary > 1000) AS everyone_above_1000

FROM employees;

everyone_above_900	everyone_above_1000
true	false

Does everyone in the department have a bonus

SELECT department,

BOOL_AND(bonus IS NOT NULL) AS all_have_bonus

FROM employees

GROUP BY department

ORDER BY department;

department	all_have_bonus
backend	false
design	true
frontend	false

Departments where everyone has a bonus

SELECT department

FROM employees

GROUP BY department

HAVING BOOL_AND(bonus IS NOT NULL);

department
design

Watch out

NULL values are skipped: BOOL_AND(bonus > 100) ignores those who have no bonus and gives true. If you want a missing bonus to count as “no”, write BOOL_AND(bonus IS NOT NULL AND bonus > 100).

If there are no rows, the result is NULL, not true or false.

Related functions

BOOL_OR COUNT CASE