PartiQL: aggregates & profiling

View .md

Part of the dialect series: answer "how many?", "what's the top ten?", and "what shape is this data, even?" without exporting to a spreadsheet. DynamoDB has no aggregates — the studio folds reads into answers client-side, with every item read disclosed and billed honestly.

You'll learn
  • Answer "how many?", "top ten?", and "what shape is this data?" in the query, not a spreadsheet
  • Fold with COUNT / SUM / AVG / MIN / MAX (and DISTINCT) — and know every folded row is billed
  • Group with GROUP BY, filter groups with HAVING, and re-aim ORDER BY / LIMIT at the result rows
  • Discover a schemaless table's shape with PROFILE TABLE

Aggregates in depth

sql 
SELECT COUNT(*) AS total, AVG(Points) AS avgFROM "stage.Points"WHERE pk = 'User#9'LIMIT 1000

DynamoDB has no aggregates: the matched read pages through (capped) and folds client-side into one result row. Run status disclosures report items and pages scanned — every matched item is read and billed, so bound the input with WHERE and LIMIT. Optional AS names each output column.

COUNT(*)
Counts matched items — the only aggregate that counts rows regardless of attribute presence.
COUNT(attr)
Counts items where `attr` is present and non-null. NULL and missing never count.
SUM / AVG
Numeric fold. Non-numeric values are skipped *with a disclosure*; the fold of an empty set is NULL, not 0.
MIN / MAX
Type-ordered comparison — booleans before numbers before text — so mixed-type attributes still produce a deterministic answer.
  • DISTINCT folds over distinct values: COUNT(DISTINCT attr) counts distinct non-null values (deep equality, per group when grouped), and SUM / AVG(DISTINCT attr) fold over them. DISTINCT is refused on MIN / MAX — it's a no-op there.
  • The target may be a document path, not just a top-level attribute — SUM(order.total), MAX(stats.p99). The native read projects the path's root and the fold navigates in; a path that resolves to MISSING is skipped like any absent value.
  • On an ungrouped aggregate, a statement LIMIT bounds the rows folded (the input), since exactly one row comes out.
  • Aggregates ride any read shape the dialect supports: key-condition Queries, GSI targets, filtered Scans (the Scan warning from Targeted reads still applies).
  • Not combinable with IN (SELECT …) in the same statement, and ? parameters don't mix with aggregates — both refuse with a message.
Try it · AggregateSample
Run it live in DynoStudio →
Executes asQuery · stage.Points → client fold
Query KeyConditionExpression: pk = 'User#9' · Projection: Points → fold: COUNT(*), AVG(Points)
The fold has no native form: the partition drains (a Query here), then COUNT/AVG compute client-side into one row.
1,284 items read across 2 pages to produce a single result row — every item is billed, even though one row comes back.
#totalavg
11,28447.6
Fetched 1284 items in 96ms · 100% efficient · 33 RCU · 1 partition

GROUP BY

sql 
SELECT Status, COUNT(*) AS n, SUM(Points) AS totalFROM "stage.Points"GROUP BY StatusORDER BY n DESCLIMIT 10

One result row per distinct key combination — grouping happens client-side during the same capped drain, keyed by deep value equality; an absent key and an explicit NULL group together. Group keys are top-level attributes — one or more, optionally AS-renamed — and GROUP BY without any aggregate is also valid (one row per distinct value, a server-billed SELECT DISTINCT over the whole match).

With GROUP BY, the trailing clauses re-aim at the result rows: ORDER BY sorts the folded groups (by key or aggregate column name) and LIMIT bounds the groups returned — ORDER BY n DESC LIMIT 10 means "top ten groups by count", exactly as written.

  • Every plain SELECT column must be a grouped attribute, and every group key must appear in the SELECT list — violations refuse with the column named.
  • Group keys may be document paths (GROUP BY address.city); an unaliased path names its column by the last step, and the matching SELECT column must be the same path.
  • Up to 10,000 distinct groups; past the cap the run refuses and recommends a narrower WHERE.

HAVING

sql 
SELECT Status, COUNT(*) AS nFROM "stage.Points"GROUP BY StatusHAVING COUNT(*) >= 2 AND Status <> 'noise'ORDER BY n DESC

HAVING filters the folded result rows — after grouping, before the re-aimed ORDER BY / LIMIT. It speaks the full expression grammar; reference an aggregate by repeating its spelling (COUNT(*)) or its AS alias, and a group key by name. Place it right after GROUP BY. Two honest rules: an aggregate used in HAVING must also appear in the SELECT list (the fold computes only what the list names), and a reference that isn't a result column refuses with the available columns named — a typo never silently empties the result. HAVING on an ungrouped fold filters the single result row.

PROFILE TABLE

sql 
PROFILE TABLE "stage.Orders"PROFILE TABLE "stage.Orders" WHERE tenantId = 'kanject' LIMIT 5000

Schema discovery over schemaless data: one result row per attribute — coverage against the sample, type distribution ("Number 94% · String 6%"), NULL vs MISSING vs empty counts, and a "mixed types" note when an attribute carries more than one shape. WHERE bounds what's sampled, LIMIT bounds the rows read, and a profile that hits the read cap returns a disclosed partial sample ("sampled the first N items — the table continues") rather than refusing — unlike an aggregate, a sample is an honest answer.

Caps for these features

  • Aggregates / GROUP BY: 100 pages · 10,000 distinct groups.
  • PROFILE TABLE: 100 pages · up to 1,000 distinct attribute names; a profile that hits the page cap returns a disclosed partial sample, not a refusal.
  • Every cap breach refuses with a named fix — typically a narrower WHERE or a smaller LIMIT.
  • On Professional, the aggregate and GROUP BY caps are tunable per install (Settings → Querying); raising one can increase a query's read cost.
AWS background
Recap
  • DynamoDB has no aggregates — the studio folds a read client-side, billing every matched item, so bound it with WHERE and LIMIT.
  • GROUP BY makes one row per distinct key; the trailing ORDER BY / LIMIT then re-aim at the folded groups ("top ten by count").
  • HAVING filters the folded rows — an aggregate it references must also appear in the SELECT list.
  • PROFILE TABLE samples a schemaless table into one row per attribute; a capped profile is an honest partial sample, not a refusal.
Try it yourself 3
1 Bound the fold
SELECT COUNT(*) FROM "stage.Points" folds over a full-table Scan. Count only user 9's rows.
An aggregate is only as cheap as its read — give the fold a key-condition Query to drain.
Show solution
With the partition-key equality the drain is a single-partition Query. The fold still runs client-side, but it now reads — and bills — only the matched partition.
sql 
SELECT COUNT(*) AS total FROM "stage.Points"WHERE pk = 'User#9'
2 Top five groups
Report the five statuses with the most items in stage.Points, largest first.
With GROUP BY, the trailing ORDER BY / LIMIT re-aim at the folded groups, not the read.
Show solution
ORDER BY n DESC LIMIT 5 means "top five groups by count", exactly as written — both trailing clauses re-aim at the result rows.
sql 
SELECT Status, COUNT(*) AS nFROM "stage.Points"GROUP BY StatusORDER BY n DESCLIMIT 5
3 Filter the groups, not the rows
Of those grouped statuses, keep only the ones carrying at least 100 items.
HAVING filters the folded result rows — and an aggregate it references must also appear in the SELECT list.
Show solution
HAVING COUNT(*) >= 100 filters after grouping, before the re-aimed ORDER BY. COUNT(*) stays in the SELECT list because the fold computes only what the list names.
sql 
SELECT Status, COUNT(*) AS nFROM "stage.Points"GROUP BY StatusHAVING COUNT(*) >= 100ORDER BY n DESC
Was this page helpful?