> Every UQL docs page, as Markdown: https://uql-orm.dev/llms.txt
> The same docs over MCP: https://uql-orm.dev/mcp
> Before writing UQL code, read the skill: https://uql-orm.dev/.well-known/agent-skills/uql-orm/SKILL.md

# Aggregate Queries

> Group rows and compute COUNT, SUM, AVG, MIN and MAX with HAVING, on every SQL engine and MongoDB.

Source: https://uql-orm.dev/querying/aggregate

`aggregate()` computes totals over the rows a query matches, or over each group of them, as SQL’s `GROUP BY` and `HAVING` do. The same query runs on every SQL engine and on MongoDB.

```ts title="You write"
import { pool } from './uql.config.js';
import { Order } from './shared/models/index.js';

const results = await pool.aggregate(Order, {
  $where: { amount: { $gt: 0 } }, // filters rows before grouping
  $group: { status: true },
  $select: {
    total: { $sum: { amount: true } },
    count: { $count: '*' },
  },
  $having: { count: { $gt: 5 } }, // filters groups after aggregation
  $sort: { total: -1 },
  $limit: 10,
});
// [{ status: 'paid', total: 5400, count: 31 }, ...]
```

PostgreSQL:

```sql
SELECT "status", SUM("amount") "total", COUNT(*) "count"
FROM "Order"
WHERE "amount" > $1
GROUP BY "status"
HAVING COUNT(*) > $2
ORDER BY SUM("amount") DESC
LIMIT 10
```

MongoDB:

```json
[
  { "$match": { "amount": { "$gt": 0 } } },
  {
    "$group": {
      "_id": { "status": "$status" },
      "total": { "$sum": "$amount" },
      "_uql_count_total": {
        "$sum": {
          "$cond": [{ "$eq": [{ "$ifNull": ["$amount", null] }, null] }, 0, 1]
        }
      },
      "count": { "$sum": 1 }
    }
  },
  {
    "$project": {
      "_id": 0,
      "status": "$_id.status",
      "total": {
        "$cond": [{ "$eq": ["$_uql_count_total", 0] }, null, "$total"]
      },
      "count": 1
    }
  },
  { "$match": { "count": { "$gt": 5 } } },
  { "$sort": { "total": -1 } },
  { "$limit": 10 }
]
```

Each result row holds the `$group` columns and the `$select` aliases. `$select` takes computed columns only: to return a plain column, group by it. Fields are named as keys, as in `findMany`, which makes a typo a compile error and lets an editor rename reach them. An alias cannot repeat a `$group` column, since both would come back under one name.

`$group` and `$select` are both optional. `$select` alone gives a grand total:

```ts
const [{ revenue }] = await pool.aggregate(Order, {
  $select: { revenue: { $sum: { amount: true } } },
});
```

`$group` alone returns each distinct value once, as `SELECT DISTINCT` does. `aggregate()` takes no `$distinct` of its own; for distinct rows of an entity, use it on a `findMany`:

```ts
const names = await pool.findMany(User, {
  $select: { name: true },
  $distinct: true,
});
```

## Aggregate functions

| `$select` entry | SQL | Type |
| - | - | - |
| `{ $count: '*' }` | `COUNT(*)` | `number` |
| `{ $count: { field: true } }` | `COUNT("field")` | `number` |
| `{ $countDistinct: { field: true } }` | `COUNT(DISTINCT "field")` | `number` |
| `{ $sum: { field: true } }` | `SUM("field")` | the column’s type, or `null` |
| `{ $avg: { field: true } }` | `AVG("field")` | `number`, or `null` |
| `{ $min: { field: true } }` | `MIN("field")` | the column’s type, or `null` |
| `{ $max: { field: true } }` | `MAX("field")` | the column’s type, or `null` |

`COUNT("field")` counts non-null values only. `$sum` and `$avg` take numeric columns, and a `$sum` over a `bigint` column is a `bigint`. Over no rows, the counts return `0` and the rest return `null`, hence the `| null`. MongoDB’s `$sum` answers `0` there, so UQL also counts the values it summed (`_uql_count_total` in the pipeline above) and returns `null` when there were none, as SQL does. Only counting has a distinct variant.

## Filtering and sorting groups

`$where` filters rows before grouping. `$having` filters the groups afterwards, by any result column, with the [comparison operators](https://uql-orm.dev/querying/comparison-operators.md) that column’s type allows. `$sort` orders by a grouped column or a computed alias, and `$skip` and `$limit` page the groups.

## Counting only some rows

A `$where` beside a function aggregates only the rows it matches, which pivots rows into columns in one query. Published posts, drafts and published points per author:

```ts title="You write"
const byAuthor = await pool.aggregate(Post, {
  $group: { authorId: true },
  $select: {
    published: { $count: '*', $where: { published: true } },
    drafts: { $count: '*', $where: { published: false } },
    publishedPoints: { $sum: { points: true }, $where: { published: true } },
  },
});
```

```sql title="PostgreSQL"
SELECT "authorId", COUNT("published") "published", COUNT("drafts") "drafts", SUM("publishedPoints") "publishedPoints"
FROM (
  SELECT "authorId",
    CASE WHEN "published" = $1 THEN 1 END "published",
    CASE WHEN "published" = $2 THEN 1 END "drafts",
    CASE WHEN "published" = $3 THEN "points" END "publishedPoints"
  FROM "Post"
) "_uql_rows"
GROUP BY "authorId"
```

The function’s `$where` takes the entity’s fields and relations, as a query’s does, so `{ $count: '*', $where: { tags: { $size: { $gte: 2 } } } }` counts the posts with two tags or more; the query’s `$where` still applies first. On MongoDB it takes every operator but `$text`, which MongoDB runs only as a pipeline’s first stage.

## Grouping by a relation’s field

A `$group` entry can reach a field on a to-one relation, at any depth, under an alias you choose. Posts per country of the author’s company:

```ts title="You write"
const byCountry = await pool.aggregate(Post, {
  $group: { country: { author: { company: { country: true } } } },
  $select: { posts: { $count: '*' }, points: { $sum: { points: true } } },
});
// [{ country: 'CO', posts: 12, points: 340 }, ...]
```

```sql title="PostgreSQL"
SELECT "author.company"."country" "country", COUNT(*) "posts", SUM("Post"."points") "points"
FROM "Post"
INNER JOIN "User" "author" ON "author"."id" = "Post"."authorId"
INNER JOIN "Company" "author.company" ON "author.company"."id" = "author"."companyId"
GROUP BY "author.company"."country"
```

Rows stay flat, keyed by the alias, which `$having` and `$sort` can use. Only to-one relations are allowed, since a to-many would multiply the totals. The join is inner, so a row whose relation is missing is left out; group by the foreign key (`{ authorId: true }`) to count it.

A [relation aggregate](https://uql-orm.dev/entities/computed-fields.md#relation-aggregates), such as an order’s item count, groups and totals like any other field. UQL reads it in a subquery and groups the result, so an `$avg` over it averages the per-order values, not the items.

## Next steps

- [Querier API](https://uql-orm.dev/querying/querier.md): find, select, sort and pagination.
- [Logical Operators](https://uql-orm.dev/querying/logical-operators.md): compose the `$where` that runs before grouping.
- [Counting](https://uql-orm.dev/querying/counting.md): counting related rows from a find.