Website logo
⌘K
🏠Homepage
👋Introduction
Key Concepts & Architecture
🚀Run Oxla in 2 minutes
🏃‍♂️Run Oxla on S3
🛫Multi-node Deployment
👨‍💻SQL Reference
SQL Queries
SQL Clauses
SQL Data Types
SQL Functions
Schema
Comment Support
⛓️SQL Mutations
DELETE
UPDATE
🚨Error Handling
🆚Differences Between Oxla vs. PostgreSQL
🔄Understanding Transactions
📈Public Metrics
⚙️Oxla Configuration File
✨Changelog
Docs powered by Archbee
SQL Reference
...
SQL Functions
Aggregation Functions

BOOL_AND

7min

Overview

The 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.

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 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);

Display the Table
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     |
+----------+-----------+---------------+-------------+-------+


#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
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            |
+--------------+


#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
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          |
+------------+




Updated 09 Oct 2023
Did this page help you?
PREVIOUS
COUNT
NEXT
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
Docs powered by Archbee

©2023 Oxla