COUNT function in Amazon QLDB
Important
End of support notice: Existing customers will be able to use Amazon QLDB until end of support on 07/31/2025. For more details, see
Migrate an Amazon QLDB Ledger to Amazon Aurora PostgreSQL
In Amazon QLDB, use the COUNT function to return the number of documents
that are defined by the given expression. This function has two variations:
-
COUNT(*)– Counts all of the documents in the target table whether or not they include null or missing values. -
COUNT(expression)– Computes the number of documents with non-null values in a specific, existing field or expression.
Warning
The COUNT function is not optimized, so we don't recommend using it
without an indexed lookup. When you run a query in QLDB without an indexed lookup,
it invokes a full table scan. This can cause performance problems on large tables,
including concurrency conflicts and transaction timeouts.
To avoid table scans, you must run statements with a WHERE predicate
clause using an equality operator (= or
IN) on an indexed field or a document ID. For more information, see
Optimizing query performance.
Syntax
COUNT ( * |expression)
Arguments
expression-
The field name or expression that the function operates on. This parameter can be any of the supported Data types.
Return type
int
Examples
SELECT COUNT(*) FROM VehicleRegistration r WHERE r.LicensePlateNumber = 'CA762X' -- 1
SELECT COUNT(r.VIN) FROM Vehicle r WHERE r.VIN = '1N4AL11D75C109151' -- 1
SELECT COUNT(a) FROM << { 'a' : 1 }, { 'a': 2 }, { 'a': 3 } >> -- 3Related functions
