# PartiQL: aggregates & profiling

Part of the [dialect series](https://www.kanject.com/docs/dynostudio-partiql/): 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 avg
FROM "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](https://www.kanject.com/docs/dynostudio-partiql-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 · Aggregate**

```sql
SELECT COUNT(*) AS total, AVG(Points) AS avg
FROM "stage.Points"
WHERE pk = 'User#9'
```

_Executes as:_ Query · stage.Points → client fold — 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.

_Run it live in DynoStudio:_ https://www.kanject.com/dynostudio/

> **An aggregate is only as cheap as its read:** `COUNT(*)` and `SUM` look free — they return one number — but DynamoDB has no aggregates, so the fold **reads and bills every matched item**. With a partition-key predicate (as above) the drain is a targeted Query. Drop it — `SELECT COUNT(*) FROM "stage.Orders"` with no `WHERE` — and you fold over a **full-table Scan**, billing for every item in the table to produce a single row. Bound the input with `WHERE` and `LIMIT`; the Scan divide is [Targeted reads](https://www.kanject.com/docs/dynostudio-partiql-reads/).

## GROUP BY

```sql
SELECT Status, COUNT(*) AS n, SUM(Points) AS total
FROM "stage.Points"
GROUP BY Status
ORDER BY n DESC
LIMIT 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 n
FROM "stage.Points"
GROUP BY Status
HAVING 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**

- [Using Global Secondary Indexes for materialized aggregation queries](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/bp-gsi-aggregation.html) — AWS docs: the native pattern for aggregates a hot path reads repeatedly — precompute and store, rather than re-folding a read each time.
- [Scanning tables in DynamoDB](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Scan.html) — AWS docs: an unbounded fold drains a Scan — this is what it bills.

**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**

**1. Bound the fold**

`SELECT COUNT(*) FROM "stage.Points"` folds over a full-table Scan. Count only user 9's rows.

_Hint:_ An aggregate is only as cheap as its read — give the fold a key-condition Query to drain.

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

_Hint:_ With `GROUP BY`, the trailing `ORDER BY` / `LIMIT` re-aim at the folded groups, not the read.

_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 n
FROM "stage.Points"
GROUP BY Status
ORDER BY n DESC
LIMIT 5
```

**3. Filter the groups, not the rows**

Of those grouped statuses, keep only the ones carrying at least 100 items.

_Hint:_ `HAVING` filters the folded result rows — and an aggregate it references must also appear in the SELECT list.

_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 n
FROM "stage.Points"
GROUP BY Status
HAVING COUNT(*) >= 100
ORDER BY n DESC
```

> **Where next?:** Reading is solved. Ready to change data? [Writes & transactions](https://www.kanject.com/docs/dynostudio-partiql-writes/) — single-item DML, multi-statement scripts, and all-or-nothing transaction blocks.

---
_Source: https://www.kanject.com/docs/dynostudio-partiql-aggregates/ · Kanject Docs_