How to COUNT, SUM and Aggregate in DynamoDB
DynamoDB has exactly one built-in aggregate: Select=COUNT, which counts matched
items and still bills every item it evaluates. There is no native SUM, AVG,
MIN or MAX, and PartiQL adds none.
Can DynamoDB do SUM, COUNT, and aggregate functions?
Only COUNT, and it is not cheap. For a SUM or an exact count you read often,
keep an aggregate item up to date as data changes, query an S3 export with Athena,
or run SQL in DynoTable's Workbench.
Select=COUNTreturns a number instead of items, but you pay the fullScanorQueryread, and a page stops at 1 MB.DescribeTable.ItemCountis free but approximate and updated only "approximately every six hours." Fine for a dashboard tile, wrong for anything exact.SUM,AVG,MIN,MAXhappen outside DynamoDB: in a maintained aggregate item, in a SQL engine over an export, or in your app after a read.
Counting items: Select=COUNT
Both Query and Scan accept a Select parameter. Set it to COUNT and the
response carries the counts instead of the items:
aws dynamodb scan \
--table-name tickets \
--select COUNT \
--filter-expression "#s = :open" \
--expression-attribute-names '{"#s":"status"}' \
--expression-attribute-values '{":open":{"S":"OPEN"}}'The response gives you two numbers (AWS: Counting the items in the results):
Count: "the number of items that remain, after a filter expression (if present) was applied."ScannedCount: "the number of items evaluated, before anyScanFilteris applied." With no filter,ScannedCountis the same asCount.
Two more gotchas that bite people counting large tables:
- The 1 MB page limit still applies. Past 1 MB, "
ScannedCountandCountrepresent only a partial count of the total items" (AWS). With an SDK, loop onLastEvaluatedKeyand keep a running total (DynamoDB pagination); the AWS CLI pages and sums both counts for you. - A narrow
Querybeats aScan.Select=COUNTon aQuerymeters only the items under one partition key. If you can pin one (base table or a GSI), count there (Query vs Scan). The Expression Builder writes the key condition.
Select=COUNT vs ItemCount (and why it's stale)
DescribeTable returns an ItemCount (and TableSizeBytes) for free, with no
read cost. The
API reference
says: "DynamoDB updates this value approximately every six hours. Recent changes
might not be reflected in this value."
Select=COUNT | DescribeTable.ItemCount | |
|---|---|---|
| Exactness | Exact (for the matched set) | Approximate |
| Freshness | Live | Updated ~every 6 hours |
| Cost | Reads + bills every evaluated item | Free (metadata) |
| Can filter / count a subset | Yes (filter expression) | No, whole table only |
Use ItemCount for a rough "how big is this table" check. Use Select=COUNT when
you need an exact, filtered or current number, and accept the read cost. For a count
that is live and cheap to read, maintain it yourself (below).
Why there's no native SUM/AVG/MIN/MAX
DynamoDB's read operations return items, and counting is the only fold the API
offers. PartiQL doesn't change this. Its SELECT takes a projection, one table and
an optional WHERE and ORDER BY
(the full grammar).
Its functions are SIZE, EXISTS, ATTRIBUTE_TYPE, BEGINS_WITH, CONTAINS
and MISSING; "any SQL functions that are not included in this list are not
currently supported"
(AWS).
An aggregate fails at parse time, before it reads anything:
SELECT SUM(minutesSpent) FROM "tickets"ValidationException: Unexpected path component at 1:8:3SELECT COUNT(*) fails the same way (at 1:8:5), with or without a key condition.
(More on the PartiQL ceiling in PartiQL vs SQL.)
Getting a real SUM, AVG or exact count
Since DynamoDB won't aggregate, you compute the number somewhere else. All four ways are worked through in DynamoDB GROUP BY:
- A maintained aggregate item.
ADDto a counter or running total in the sameTransactWriteItemsas the write, so the two can't drift (Pattern 1). Reading it is oneGetItem.AVGis the stored sum divided by the stored count. - A Streams + Lambda rollup, when you can't touch the write path. It has to handle inserts, updates and deletes, and retries must not double-count (Pattern 2).
- An export to S3, queried with Athena. Exports "don't consume read capacity units (RCUs)" and need point-in-time recovery turned on (AWS; Pattern 4).
- An app-side fold. Page through the items and add them up. It reads every item
every time, the same cost as
Select=COUNTplus the data transfer.
Running COUNT/SUM/AVG in DynoTable's SQL Workbench
When you just need the answer ("how many OPEN tickets, and how many minutes have they taken?"), DynoTable's SQL Workbench runs real aggregates without a loop or a Lambda.
It reads your table through DynamoDB's Query/Scan, then runs the SELECT on the
rows: SQL within DynamoDB's access-pattern rules.
-- Runs in the DynoTable Workbench (NOT in PartiQL):
SELECT status,
COUNT(*) AS tickets,
SUM(minutesSpent) AS minutes,
AVG(minutesSpent) AS avg_minutes,
MIN(minutesSpent) AS shortest,
MAX(minutesSpent) AS longest
FROM tickets
GROUP BY status
ORDER BY minutes DESCThat's COUNT, SUM, AVG, MIN, MAX, GROUP BY, and ORDER BY on a computed
aggregate in one statement. PartiQL can express none of it; its ORDER BY only
takes key attributes.
Workbench limits:
- The rows still come through DynamoDB's
Query/Scan. With no key condition, thisGROUP BYis a fullScan, and the status bar estimates that read before you run (Query vs Scan). - An Execute fetches the first page. The aggregate columns show a
partialbadge until the last page lands; Run to end (⌘⇧↩) reads them all. - Aggregates run on scalar attributes after the rows land.
FAQ
Can I count items in DynamoDB without scanning?
Not for a whole table. An exact, current count means reading the items:
Select=COUNT still meters every item it evaluates. Without a scan you get the
approximate DescribeTable.ItemCount (updated ~every 6 hours) or a counter item you
maintain yourself on each write. For one partition, a Query with Select=COUNT is
exact and reads only that partition.
How do I count items by a GSI?
Run Query (or Scan) against the index with Select=COUNT. Counting via a
narrow GSI partition is far cheaper than scanning the base table, because you only
read the items in that index partition. Model the index around the count you need.
Is DescribeTable.ItemCount accurate?
It's approximate. The
API reference
states DynamoDB updates ItemCount and TableSizeBytes "approximately every six
hours," and "recent changes might not be reflected in this value." Don't use it where
an exact or live number matters.
Can DynamoDB do SUM or AVG?
Not natively, and not in PartiQL, which has no aggregate functions. Maintain a running
total (optionally via DynamoDB Streams), query an S3 export with Athena, or run the
SUM/AVG in DynoTable's SQL Workbench.
What's the difference between Count and ScannedCount?
ScannedCount is how many items DynamoDB evaluated before your filter; Count is
how many remain after it. They're equal when there's no filter expression. A big
gap between them means an inefficient count.
Need to sum, average, or group your DynamoDB data without writing a scan loop? Download DynoTable and run it in a Workbench tab. Comparing clients first? See where it lands against a plain DynamoDB GUI.