DynamoDB GROUP BY: How to Aggregate Instead
There is no GROUP BY in DynamoDB, and no SUM, AVG, MIN or MAX. The one
built-in aggregate is a count of matched items, Select=COUNT, and it still reads
every item it evaluates. PartiQL adds none of them.
Can you GROUP BY in DynamoDB?
No. Neither the API nor PartiQL can group or sum, so you decide where the aggregate is computed: on write, downstream (a Streams rollup or an S3 export), or after a read.
- Pre-compute on write. DynamoDB "doesn't natively support aggregation operations
like
SUMorCOUNTacross items," so AWS says to pre-compute them as data changes (AWS: materialized aggregation). - Report from an export. Export the table to S3 and run the
GROUP BYin Athena. The export reads no table capacity. - Group after a read when the read is one partition. Over a full
Scanyou pay for the whole table on every run. - Explore in DynoTable's SQL Workbench, which runs
GROUP BYclient-side over the rows it reads from a live table.
Why aggregation is hard in DynamoDB
Query and Scan return items; they don't fold them into numbers. A Scan reads
the table 1 MB per page.
"DynamoDB considers the size of the items that are evaluated, not the size of the
items returned"
(AWS),
so a FilterExpression narrows the result without lowering the bill.
PartiQL inherits the same engine. Its SELECT takes only WHERE and ORDER BY
(the grammar, with the rejections reproduced),
so a grouped statement fails before it reads anything:
SELECT COUNT(*) FROM "tickets" GROUP BY statusValidationException: Unsupported clause: GROUP BYFor the rest of the gap between PartiQL and SQL, see PartiQL vs SQL.
Pattern 1: aggregate on write (atomic counters)
If you know the groups ahead of time (count per status, minutes per account, downloads per month), keep a counter item and update it on every write.
Use an ADD update expression. It works on numbers and sets
and "avoids read-modify-write race conditions," so two writers incrementing the same
counter never clobber each other
(AWS):
UpdateItem
Key { pk: "STATS#tickets", sk: "status#closed" }
UpdateExpression "ADD ticketCount :one"
ExpressionAttributeValues { ":one": 1 }
This is your SELECT COUNT(*) … GROUP BY status, already computed and readable in
one GetItem. The cost: you must know the grouping key at write time, and the
counter update rides the write path.
Keep the write and the counter together. Two separate calls drift if your app
crashes between them. Put both in one TransactWriteItems: "either all of them
succeed, or all of them fail"
(AWS).
TransactWriteItems ClientRequestToken: ticket-1001
Put Key { pk: "TICKET#1001", sk: "TICKET" }, status: "OPEN", minutesSpent: 120
ConditionExpression attribute_not_exists(pk)
Update Key { pk: "STATS#tickets", sk: "status#open" }
UpdateExpression ADD ticketCount :one
Values :one = 1Make retries safe. ADD is not idempotent: a retry after a network error can
count twice. The ClientRequestToken makes an identical retry a no-op for 10
minutes, and attribute_not_exists cancels any later replay of the ticket.
Move the count when the group changes. That guard only covers creating the
ticket. When it goes from OPEN to CLOSED, update the ticket and both counters in
one transaction, conditioned on the old status:
TransactWriteItems ClientRequestToken: ticket-1001-close
Update Key { pk: "TICKET#1001", sk: "TICKET" }
UpdateExpression SET #s = :new
ConditionExpression #s = :old
Names #s = status
Values :new = "CLOSED", :old = "OPEN"
Update Key { pk: "STATS#tickets", sk: "status#open" }
UpdateExpression ADD ticketCount :minusOne
Values :minusOne = -1
Update Key { pk: "STATS#tickets", sk: "status#closed" }
UpdateExpression ADD ticketCount :one
Values :one = 1The condition also cancels a replay: once the ticket is CLOSED, the same move
fails with ConditionalCheckFailed and neither counter changes. A delete has the
same shape: Delete the ticket with #s = :old as its condition, plus
ADD ticketCount :minusOne on that status's counter. Skip either path and the totals drift.
A transactional write costs 2 write units per item instead of 1. For a conditional
SET without a transaction, see atomic counters.
If one counter takes most of the writes, shard it across N items and sum them on read (hot partitions shows how).
Pattern 2: DynamoDB Streams + Lambda rollups
When you don't want aggregation logic on the write path, or the write is a plain
PutItem you can't wrap, move it downstream. AWS calls this materialized
aggregation
(AWS: Using GSIs for materialized aggregation queries):
- The app writes the raw item (a ticket, a download, an event) with no aggregation logic.
- DynamoDB Streams captures the write as a stream record.
- A Lambda attached to the stream derives the group (status, month, category…) and
ADDs to the matching aggregate item. - You read the pre-computed aggregate, often through a sparse GSI
that indexes only the rollup items, so "top 10 this month" is one
QuerywithLimit 10.
Only the aggregate items carry the indexed attribute (e.g. Month), so the raw rows
stay out of the index, "a small fraction of the total items in the table."
Handle all three event types. AWS's sample counts only INSERTs, which fits
immutable downloads. A ticket that moves from OPEN to CLOSED arrives as a
MODIFY, and a deleted one as a REMOVE.
Set the stream's StreamViewType to NEW_AND_OLD_IMAGES so each record carries
both images, then move the count:
import time
import boto3
ddb = boto3.client("dynamodb")
STATS_TABLE = "TicketStats"
def handler(event, context):
for r in event["Records"]:
old = r["dynamodb"].get("OldImage", {}).get("status", {}).get("S")
new = r["dynamodb"].get("NewImage", {}).get("status", {}).get("S")
if old == new:
continue # MODIFY that didn't move the ticket between groups
ops = [{"Put": { # marker: apply each stream record once
"TableName": STATS_TABLE,
"Item": {"pk": {"S": "APPLIED#" + r["eventID"]}, "sk": {"S": "-"},
"expiresAt": {"N": str(int(time.time()) + 2 * 86400)}},
"ConditionExpression": "attribute_not_exists(pk)"}}]
for status, delta in ((old, "-1"), (new, "1")):
if status:
ops.append({"Update": {
"TableName": STATS_TABLE,
"Key": {"pk": {"S": "STATS#tickets"}, "sk": {"S": "status#" + status}},
"UpdateExpression": "ADD ticketCount :d",
"ExpressionAttributeValues": {":d": {"N": delta}}}})
try:
ddb.transact_write_items(TransactItems=ops)
except ddb.exceptions.TransactionCanceledException as e:
if e.response["CancellationReasons"][0]["Code"] != "ConditionalCheckFailed":
raise # a real failure: let Lambda retry the batch
# the marker exists: this record was already countedThe marker makes the rollup exact. With a bare ADD, "a retry would increment the
count more than once," and Lambda retries a whole batch on an error. Here a replayed
record fails the marker's condition and its transaction cancels.
The rollups live in their own table, so the Lambda doesn't trigger on its own
writes. Enable TTL on expiresAt to clear old markers; stream records only live 24
hours, so two days of markers covers every possible replay.
Don't let a bad record stall the stream. By default, "a bad record can block processing on the affected shard for up to one day" (AWS Lambda). Cap the retries, split failing batches, and keep what fails:
aws lambda update-event-source-mapping \
--uuid <mapping-uuid> \
--maximum-retry-attempts 5 \
--maximum-record-age-in-seconds 3600 \
--bisect-batch-on-function-error \
--destination-config '{"OnFailure":{"Destination":"arn:aws:s3:::my-failed-batches"}}'An S3 destination receives the whole invocation record; SQS and SNS get metadata only. Replay what landed there, or rebuild a drifted total with a recount (Pattern 3 or 4).
To keep the change history itself beyond 24 hours, see the durable archive in the Streams guide.
The rollup is eventually consistent: AWS notes "a delay of a few seconds between a download being recorded and the aggregation being updated." For dashboards, leaderboards and trend counters that's fine.
Pattern 3: app-side grouping after Scan/Query
Or read the items and group them in your code:
groups = {}
resp = table.scan() # or query() for one partition
while True:
for item in resp["Items"]:
key = item.get("status") # None for items without one
groups[key] = groups.get(key, 0) + 1
if "LastEvaluatedKey" not in resp:
break
resp = table.scan(ExclusiveStartKey=resp["LastEvaluatedKey"])Over a full Scan this reads every item in the table on every run, and latency
grows with the table. AWS lists "scan and count at read time" as "only suitable for
very small datasets where latency isn't a concern"
(AWS).
Scoped to one partition with Query (the tickets of one account), app-side grouping
is reasonable: you read one item collection. See
Query vs Scan for the gap between the two.
A full Scan of a 1 GB table is about 131,000 read request units
eventually consistent (1,048,576 KB ÷ 4 KB × 0.5): about $0.016 at us-east-1
on-demand list price, double that strongly consistent.
Size an item with the item-size calculator, then price your table in the pricing calculator.
Pattern 4: export to S3, then GROUP BY in Athena
For recurring reports, run the SQL on a copy. Turn on point-in-time recovery, which the export requires, then export:
aws dynamodb export-table-to-point-in-time \
--table-arn arn:aws:dynamodb:us-east-1:123456789012:table/tickets \
--s3-bucket my-export-bucket \
--export-format DYNAMODB_JSONExports "don't consume read capacity units (RCUs)." A full export is billed per GB of table size, an incremental export per GB of change data for the period you pick (AWS).
Point an Athena table at the export's data/ folder. Each line is one item in
DynamoDB JSON, so every attribute is a struct keyed by its type
(AWS example):
CREATE EXTERNAL TABLE tickets_export (
Item struct<status: struct<S: string>, minutesSpent: struct<N: string>>
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://my-export-bucket/AWSDynamoDB/<export-id>/data/';
SELECT Item.status.S AS status,
COUNT(*) AS tickets,
SUM(CAST(Item.minutesSpent.N AS DECIMAL(18, 2))) AS minutes
FROM tickets_export
GROUP BY Item.status.S;Each export lands under a new ID, so repoint the table after the next one:
ALTER TABLE tickets_export
SET LOCATION 's3://my-export-bucket/AWSDynamoDB/<new-export-id>/data/';The numbers are as fresh as the export.
For an ad-hoc query on the live table, or a JOIN to another data source, the
Amazon Athena DynamoDB connector runs SQL over the table itself
(setup in SQL for DynamoDB).
It scans as it goes, and AWS warns those scans "can consume a large number of read capacity units" (AWS).
Which pattern do I use?
| You need… | Use |
|---|---|
| A known group total on a hot read path | Pattern 1: atomic counter (ADD) |
| Aggregates without touching the write path | Pattern 2: Streams + Lambda rollup |
| Exact totals, no drift | Pattern 1 on every write path, or Pattern 2 with the marker |
| A count scoped to one partition | Pattern 3: Query, then group in your app |
| Recurring BI or reporting in SQL | Pattern 4: export to S3 + Athena |
A one-off GROUP BY against live data | DynoTable Workbench or the Athena connector |
Running GROUP BY directly in DynoTable's SQL Workbench
The patterns above serve aggregates in production. When you're exploring a table ("how many tickets per status, right now?"), you don't want to deploy a Lambda or an export. You want to type the query.
DynoTable's SQL Workbench runs GROUP BY, COUNT, SUM, AVG, HAVING and
JOIN against your live tables, client-side over the rows it reads. It's the SQL
DynamoDB's PartiQL endpoint rejects:
SELECT status, COUNT(*) AS tickets, SUM(minutesSpent) AS minutes
FROM "tickets"
GROUP BY status
HAVING SUM(minutesSpent) > 1000
ORDER BY minutes DESCIt reads items the way the API allows (Query when your WHERE sets the partition
key or a GSI partition key with =, Scan otherwise), then groups them: Pattern 3 without the loop, within DynamoDB's
access-pattern rules. The status bar estimates the read before you run.
An Execute fetches the first page, and the aggregate columns carry a partial badge
while more pages remain. Run to end (⌘⇧↩) reads every page for exact
totals. For a hot read path, pre-compute (Pattern 1 or 2).
For cross-table joins, which PartiQL can't run either, see DynamoDB JOIN. Comparing GUI clients on this capability? See the DynamoDB GUI comparison.
FAQ
Does DynamoDB PartiQL support GROUP BY?
No. DynamoDB's PartiQL SELECT supports WHERE and ORDER BY only: no GROUP BY,
HAVING, aggregate functions, or JOIN. The grammar is
documented
as SELECT … FROM … [WHERE …] [ORDER BY …].
Can I GROUP BY the partition key?
Not in DynamoDB or PartiQL. If "per partition key" is a known access pattern,
maintain one aggregate item per key with an atomic ADD (Pattern 1), or roll it
up with Streams + Lambda (Pattern 2).
How do I do SUM or AVG per group?
SUM: keep a running total per group and ADD to it on write. AVG: store the
sum and the count, and divide at read time. For a one-off AVG, use DynoTable's
SQL Workbench or the Athena DynamoDB connector.
Is there a partiql group by workaround?
No PartiQL-side one. Pre-compute the aggregate (counters or Streams) and SELECT
the rollup item, or run the GROUP BY elsewhere: DynoTable's Workbench for ad-hoc
questions, Athena over an S3 export for recurring reports.
Want to run GROUP BY against your own tables without writing a Lambda?
Try DynoTable and point the SQL Workbench at a live table.