How to COUNT, SUM and Aggregate in DynamoDB

DynamoDB has exactly one built-in aggregate: counting matched items with Select=COUNT. There's no native SUM, AVG, MIN, or MAX. And even the count you can get reads (and bills for) every item it counted. This guide covers what's actually supported, the approximations people reach for, and how to run real COUNT/SUM/AVG over a table when you need them.

Can DynamoDB do SUM, COUNT, and aggregate functions?

Mostly no. DynamoDB's only built-in aggregate is Select=COUNT, which returns matched-item counts but still reads (and bills for) every item. There is no native SUM, AVG, MIN, or MAX, and PartiQL adds none either. For real aggregates with GROUP BY, fold them in your app, maintain a counter, or run SQL in DynoTable's Workbench.

• Select=COUNT returns the number of matched items, but DynamoDB still reads every item to produce it — you pay the full Scan/Query read cost, not a cheap "count" cost.
• There is no native SUM, AVG, MIN, or MAX. DynamoDB's read operations return items; they don't fold them into a number. PartiQL doesn't add aggregates either.
• DescribeTable.ItemCount is free but approximate and updated only "approximately every six hours" — fine for a dashboard tile, wrong for anything exact.
• For exact COUNT/SUM/AVG/MIN/MAX (with GROUP BY), aggregate in your app, maintain a counter, or run it in DynoTable's SQL Workbench (below).

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 Orders \
  --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.

If you have only the partition key and need to count duplicates within it, the condition + filter you pass is exactly what the DynamoDB Expression Builder generates — the KeyConditionExpression, FilterExpression, and ExpressionAttributeNames/Values maps above — without hand-escaping the JSON.

Two more gotchas that bite people counting large tables:

• The 1 MB page limit still applies. "If the size of the Scan result set is larger than 1 MB, ScannedCount and Count represent only a partial count of the total items" (AWS Scan docs). You have to paginate with LastEvaluatedKey → ExclusiveStartKey and keep a running total to get the real number — the same loop covered in DynamoDB pagination.
• A narrow Query beats a Scan. Select=COUNT on a Query meters only the items in the targeted partition, not the whole table. If you can pin a partition key (base table or a GSI), count there — it's the Query-vs-Scan cost gap applied to counting.

Select=COUNT vs ItemCount (and why it's stale)

DescribeTable returns an ItemCount (and TableSizeBytes) for free, with no read cost. The catch is in the API reference itself: "DynamoDB updates this value approximately every six hours. Recent changes might not be reflected in this value." So it can lag well behind your table's actual state.

Use ItemCount for a rough "how big is this table" gut check or a dashboard tile. Use Select=COUNT when you need an exact, filtered, or current number — and accept the read cost. For anything truly live and free, track a counter yourself (see Aggregation patterns below).

Why there's no native SUM/AVG/MIN/MAX

DynamoDB's read operations return items. There's no query planner to fold a result set into a scalar, so there's nothing to compute a SUM or AVG with. Counting is the only fold the API offers, via Select=COUNT.

PartiQL doesn't change this. The PartiQL SELECT grammar is SELECT {{expression}} [, …] FROM {{table}}[.{{index}}] [WHERE …] [ORDER BY {{key}} …], where the expression is "a projection formed from the * wildcard or a projection list of one or more attribute names or document paths." There is no aggregate function and no GROUP BY clause in that grammar — and ORDER BY takes a {{key}}, documented as "a hash key or a sort key to use to order returned results." Every PartiQL SELECT still compiles to a GetItem, Query, or Scan, so SELECT SUM(total) FROM "Orders" simply isn't expressible. (More on the PartiQL ceiling in PartiQL vs SQL.)

Aggregation patterns (counters, streams, app-side)

Since DynamoDB won't aggregate for you, the established patterns push the work elsewhere:

• Maintained counter item. Keep a dedicated item (e.g. PK = "STATS#orders") and ADD to a numeric attribute on every write with an UpdateItem. Reading the aggregate is then a single GetItem — exact and cheap, but you own the increment logic, its consistency, and the contention if one counter gets hammered.
• DynamoDB Streams → aggregator. Enable a stream and wire it to a Lambda that updates running totals (counts, sums) as items change. Per the AWS Streams docs, you can configure the stream's StreamViewType so each record carries the NEW_AND_OLD_IMAGES — "both the new and the old images of the item" — enough to keep SUM-style aggregates current without re-scanning. Stream records are subject to a 24-hour lifetime ("the stream records within a shard are removed automatically after 24 hours"), so the consumer has to keep up.
• App-side fold. Page through the matched items and accumulate the SUM/AVG/MIN/MAX in your own code. Correct, but it reads (and bills for) every item every time — the same cost profile as Select=COUNT, plus the data transfer.
• Offload to analytics. For heavy or ad-hoc analytical aggregation, export the table to S3 and query it with Athena, or stream it into a warehouse. Per the AWS export-to-S3 docs, exporting "doesn't consume read capacity units" and lets you "perform analytics and complex queries using AWS services such as Athena" — the AWS-recommended path once you've outgrown per-request aggregation.

Each trades simplicity for either write-time bookkeeping (counters, streams) or read-time cost (app-side scans). No pattern makes DynamoDB itself compute a SUM for free. The grouping version of this tradeoff — aggregating per key rather than over the whole table — is its own guide: DynamoDB GROUP BY.

Running COUNT/SUM/AVG in DynoTable's SQL Workbench

When you just need the answer — "how many OPEN orders, and what's their total" — without writing a paginating scan loop or a Lambda, DynoTable's SQL Workbench runs real aggregates. It materializes your tables through DynamoDB's actual Query/Scan runtime, then runs your full SQL on top: SQL within DynamoDB's access-pattern rules.

-- Runs in the DynoTable Workbench (NOT in PartiQL):
SELECT status,
       COUNT(*)        AS orders,
       SUM(total)      AS revenue,
       AVG(total)      AS avg_order,
       MIN(total)      AS smallest,
       MAX(total)      AS largest
FROM orders
GROUP BY status
ORDER BY revenue DESC

That's COUNT, SUM, AVG, MIN, MAX, GROUP BY, and ORDER BY — none of which DynamoDB or PartiQL can express — in one statement. This is the same analytical wedge as SQL for DynamoDB; for the full grouping story see DynamoDB GROUP BY.

The Workbench is honest about the access model underneath, not a pretend-Postgres:

• The rows still come through DynamoDB's real Query/Scan. A GROUP BY over a whole table is still a Scan underneath — the Workbench surfaces that cost rather than hiding it, the same Query-vs-Scan tradeoff.
• Aggregates run on the materialized scalar attributes after the rows land.

FAQ

Can I count items in DynamoDB without scanning? Not exactly. For an exact, current count you must read the items — Select=COUNT still meters every counted item. The only no-scan options are the approximate DescribeTable.ItemCount (updated ~every 6 hours) or a counter item you maintain yourself on each write.

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 — the PartiQL SELECT grammar has no aggregate functions. Aggregate in your application, maintain a counter (optionally via DynamoDB Streams), 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.