BOOL_AND
Overview
The BOOL_AND()
function calculates all the boolean values in the aggregated group, which will have these results:
-
true
if all the values aretrue
for every row. -
false
if at least one row in the group isfalse
.
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.Examples
In 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 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);
SELECT * FROM payment;
The above query will show the following 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 |
+----------+-----------+---------------+-------------+-------+
#Case 1: BOOL_AND
with a false result
We will find out if all customers have paid for their orders using the query below:
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.
+--------------+
| final_result |
+--------------+
| f |
+--------------+
#Case 2: BOOL_AND
with a true result
We will find out if Maya has paid for her orders using the query below:
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.
+------------+
| maya_paid |
+------------+
| t |
+------------+