Intermediate11 min read

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 SUM or COUNT across 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 BY in Athena. The export reads no table capacity.
  • Group after a read when the read is one partition. Over a full Scan you pay for the whole table on every run.
  • Explore in DynoTable's SQL Workbench, which runs GROUP BY client-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 status
ValidationException: Unsupported clause: GROUP BY

For 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 . 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 = 1

Make 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 = 1

The 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):

  1. The app writes the raw item (a ticket, a download, an event) with no aggregation logic.
  2. captures the write as a stream record.
  3. A Lambda attached to the stream derives the group (status, month, category…) and ADDs to the matching aggregate item.
  4. You read the pre-computed aggregate, often through a that indexes only the rollup items, so "top 10 this month" is one Query with Limit 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 counted

The 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_JSON

Exports "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 pathPattern 1: atomic counter (ADD)
Aggregates without touching the write pathPattern 2: Streams + Lambda rollup
Exact totals, no driftPattern 1 on every write path, or Pattern 2 with the marker
A count scoped to one partitionPattern 3: Query, then group in your app
Recurring BI or reporting in SQLPattern 4: export to S3 + Athena
A one-off GROUP BY against live dataDynoTable 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 DESC

It 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.

By Dmitriy · Updated