> 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

# Counting

> Count matching rows, test existence, page with a total, and count or rank by a relation's size, on every SQL dialect and MongoDB.

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

UQL has five ways to ask “how many”, each at a different cost:

| You need | Use | Costs |
| - | - | - |
| How many match | `count(Entity, query?)` | one statement |
| Whether *any* match | `exists(Entity, query?)` | one statement, stops at the first row |
| A page **and** its total | `findManyAndCount(Entity, query)` | one statement on SQL in most cases, two on MongoDB |
| How many rows a relation holds | `$count` on a read | part of the read’s own statement |
| A rough size of a huge table | `estimatedCount(Entity)` | no scan at all |

## count

```ts
await pool.count(User, { $where: { active: true } });
```

With `$skip` or `$limit`, `count` counts that page only, which caps the work on a table too big to scan:

```ts
await pool.count(User, { $limit: 1000 }); // "1,000+ matches" without counting the rest
await pool.count(User, { $skip: 20 }); // how many remain after the first 20
```

A paged count runs as `SELECT COUNT(*) FROM (SELECT ... LIMIT 1000)`, so no row leaves the database. `count` takes no `$sort`, since order never changes how many rows match.

## exists

`exists` is a count capped at one row, so the engine stops at the first match. Prefer it to `count(...) > 0`:

```ts
if (await pool.exists(User, { $where: { email } })) {
  throw new Error('email already registered');
}
```

## findManyAndCount

The page plus how many rows matched in total, which is what a paginated list needs:

```ts
const [users, total] = await pool.findManyAndCount(User, {
  $where: { active: true },
  $sort: { createdAt: -1 },
  $skip: 40,
  $limit: 20,
});
// users.length === 20, total === 1247
```

On SQL the total rides in a column of the page itself (`COUNT(*) OVER ()`), so rows and total come from one statement and always agree. A second statement counts instead for an empty page, which has no row to carry the total, for a `$distinct` read, for a `$lock` on the Postgres family, and always on MongoDB. With `$distinct`, the total counts the distinct rows.

## Counting relations

`$count` returns how many rows a to-many relation holds, without loading them. The tallies arrive under `_count`, and each can take a filter of its own:

```ts
const users = await pool.findMany(User, {
  $select: { name: true },
  $count: { posts: { $where: { published: true } } },
});
// [{ name: 'Ada', _count: { posts: 12 } }]
```

Counting and populating the same relation are independent, so you can load the newest five posts and still know how many there are:

```ts
const [user] = await pool.findMany(User, {
  $select: { name: true },
  $populate: { posts: { $sort: { createdAt: -1 }, $limit: 5 } },
  $count: { posts: true },
});
user.posts.length; // 5
user._count.posts; // 128
```

Each tally is a correlated count inside the read’s own statement, and it works with `$distinct` and a raw `$select`. For a tally you always want, or for `sum`, `min`, `max` and `avg`, declare a [relation aggregate](https://uql-orm.dev/entities/computed-fields.md#relation-aggregates) field instead, which `$where` and `$sort` can also use.

## Ordering by a relation’s size

`$sort` can rank rows by a tally, for a top-N:

```ts
const busiest = await pool.findMany(User, {
  $sort: { posts: { $count: -1 } },
  $limit: 10,
});
```

To *filter* by a relation’s size instead, use [`$size`](https://uql-orm.dev/querying/relations.md#filtering-by-count-size) in `$where`: `{ posts: { $size: { $gte: 5 } } }`.

## estimatedCount

```ts
await pool.estimatedCount(User); // 2_400_000, instantly
```

It reads the row count the engine already keeps, so it answers in constant time on any table: PostgreSQL’s `pg_class.reltuples`, CockroachDB’s table statistics, MySQL and MariaDB’s `information_schema.TABLES`, SQL Server’s `sys.partitions`, MongoDB’s `estimatedDocumentCount`. Use it only where an approximation is fine:

- It is as stale as the engine’s last statistics refresh (`ANALYZE` on PostgreSQL).
- It takes no filter, so soft-deleted rows and every [query filter](https://uql-orm.dev/querying/filters.md) are inside the number.
- The SQLite family (SQLite, libSQL, Turso, D1) keeps no such statistic, so it throws there.
- The browser client does not have it.