BOOL_AND

9min
You’ll learn how to use the BOOL_AND() function to aggregate boolean values.
OverviewThe BOOL_AND() function calculates all the boolean values in the aggregated group, which will have these results:
true if all the values are true for every row.
false if at least one row in the group is false.
The input and the return type must be in BOOL.
💡NULL values are not aggregated, so it will return NULL if there are zero input rows.
ExamplesIn this example, we will use a payment table that stores details of the orders, whether the order has been paid or unpaid by the customer:
Create a Table
|
CREATE TABLE payment (
    orderid int,
    custname text,
    orderproduct text,
    ordertotal real,
    paid boolean
);
INSERT INTO payment (orderid, custname, orderproduct, ordertotal, paid)
VALUES 
(9557411, 'Maya', 'Jeans', 10.5, true),
(9557421, 'Aaron', 'T-Shirt', 9.2, true),
(9557451, 'Alex', 'Hat', 10.8, true),
(9557311, 'Will', 'Hat', 8.5, true),
(9557321, 'Will', 'T-Shirt', 12.15, true),
(9557351, 'Maya', 'T-Shirt', 9.5, true),
(9557221, 'Maya', 'Jeans', 11.02, true),
(9557251, 'Alex', 'Jeans', 11.09, true),
(9557231, 'Aaron', 'Hat', 14.56, false),
(9557281, 'Aaron', 'Hat', 12.15, true),
(9557291, 'Will', 'T-Shirt', 13.1, true);
CREATE TABLE payment (
    orderid int,
    custname text,
    orderproduct text,
    ordertotal real,
    paid boolean
);
INSERT INTO payment (orderid, custname, orderproduct, ordertotal, paid)
VALUES 
(9557411, 'Maya', 'Jeans', 10.5, true),
(9557421, 'Aaron', 'T-Shirt', 9.2, true),
(9557451, 'Alex', 'Hat', 10.8, true),
(9557311, 'Will', 'Hat', 8.5, true),
(9557321, 'Will', 'T-Shirt', 12.15, true),
(9557351, 'Maya', 'T-Shirt', 9.5, true),
(9557221, 'Maya', 'Jeans', 11.02, true),
(9557251, 'Alex', 'Jeans', 11.09, true),
(9557231, 'Aaron', 'Hat', 14.56, false),
(9557281, 'Aaron', 'Hat', 12.15, true),
(9557291, 'Will', 'T-Shirt', 13.1, true);
﻿
Display the Table
|
SELECT * FROM payment;
SELECT * FROM payment;
﻿
The above query will show the following table:
The payment Table
|
+----------+-----------+---------------+-------------+-------+
| orderid  | custname  | orderproduct  | ordertotal  | paid  |
+----------+-----------+---------------+-------------+-------+
| 9557411  | Maya      | Jeans         | 10.5        | t     |
| 9557421  | Aaron     | T-Shirt       | 9.2         | t     |
| 9557451  | Alex      | Hat           | 10.8        | t     |
| 9557311  | Will      | Hat           | 8.5         | t     |
| 9557321  | Will      | T-Shirt       | 12.15       | t     |
| 9557351  | Maya      | T-Shirt       | 9.5         | t     |
| 9557221  | Maya      | Jeans         | 11.02       | t     |
| 9557251  | Alex      | Jeans         | 11.09       | t     |
| 9557231  | Aaron     | Hat           | 14.56       | f     |
| 9557281  | Aaron     | Hat           | 12.15       | t     |
| 9557291  | Will      | T-Shirt       | 13.1        | t     |
+----------+-----------+---------------+-------------+-------+
+----------+-----------+---------------+-------------+-------+
| orderid  | custname  | orderproduct  | ordertotal  | paid  |
+----------+-----------+---------------+-------------+-------+
| 9557411  | Maya      | Jeans         | 10.5        | t     |
| 9557421  | Aaron     | T-Shirt       | 9.2         | t     |
| 9557451  | Alex      | Hat           | 10.8        | t     |
| 9557311  | Will      | Hat           | 8.5         | t     |
| 9557321  | Will      | T-Shirt       | 12.15       | t     |
| 9557351  | Maya      | T-Shirt       | 9.5         | t     |
| 9557221  | Maya      | Jeans         | 11.02       | t     |
| 9557251  | Alex      | Jeans         | 11.09       | t     |
| 9557231  | Aaron     | Hat           | 14.56       | f     |
| 9557281  | Aaron     | Hat           | 12.15       | t     |
| 9557291  | Will      | T-Shirt       | 13.1        | t     |
+----------+-----------+---------------+-------------+-------+
﻿
#Case 1: BOOL_AND with a false resultWe will find out if all customers have paid for their orders using the query below:
Select bool_and
|
SELECT BOOL_AND(paid) AS "final_result" FROM payment;
SELECT BOOL_AND(paid) AS "final_result" FROM payment;
﻿
In the BOOL_AND function, if there is at least one FALSE value, the overall result will be FALSE. The final output shows that there is an order that hasn’t been paid.
Output
|
+--------------+
| final_result |
+--------------+
| f            |
+--------------+
+--------------+
| final_result |
+--------------+
| f            |
+--------------+
﻿
#Case 2: BOOL_AND with a true resultWe will find out if Maya has paid for her orders using the query below:
Select bool_and
|
SELECT BOOL_AND(paid) AS Maya_Paid
FROM payment
WHERE custname ='Maya';
SELECT BOOL_AND(paid) AS Maya_Paid
FROM payment
WHERE custname ='Maya';
﻿
In the BOOL_AND function, if all values are TRUE, then the overall result will be TRUE. The final output shows that Maya has paid all her orders.
Output
|
+------------+
| maya_paid  |
+------------+
| t          |
+------------+
+------------+
| maya_paid  |
+------------+
| t          |
+------------+
﻿
﻿
Updated 11 May 2023
Did this page help you?
Yes
COUNT
BOOL_OR
Docs powered by archbee
TABLE OF CONTENTS
Overview
Examples
#Case 1: BOOL_AND with a false result
#Case 2: BOOL_AND with a true result