Beginner6 min read

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=COUNT returns a number instead of items, but you pay the full Scan or Query read, and a page stops at 1 MB.
  • DescribeTable.ItemCount is free but approximate and updated only "approximately every six hours." Fine for a dashboard tile, wrong for anything exact.
  • SUM, AVG, MIN, MAX happen 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 any ScanFilter is applied." With no filter, ScannedCount is the same as Count.

Two more gotchas that bite people counting large tables:

  • The 1 MB page limit still applies. Past 1 MB, "ScannedCount and Count represent only a partial count of the total items" (AWS). With an SDK, loop on LastEvaluatedKey and keep a running total (DynamoDB pagination); the AWS CLI pages and sums both counts for you.
  • A narrow Query beats a Scan. Select=COUNT on a Query meters only the items under one . 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=COUNTDescribeTable.ItemCount
ExactnessExact (for the matched set)Approximate
FreshnessLiveUpdated ~every 6 hours
CostReads + bills every evaluated itemFree (metadata)
Can filter / count a subsetYes (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:3

SELECT 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. ADD to a counter or running total in the same TransactWriteItems as the write, so the two can't drift (Pattern 1). Reading it is one GetItem. AVG is 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=COUNT plus 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 DESC

That'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, this GROUP BY is a full Scan, and the status bar estimates that read before you run (Query vs Scan).
  • An Execute fetches the first page. The aggregate columns show a partial badge 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.

By Dmitriy · Updated