# Aggregates & scalars

Run real SQL COUNT, SUM, AVG, MIN, MAX, and single-value scalar reads. Server-side, parameterized, and fully instrumented, from the query builder or the database context.

Aggregates run as a single server-side scalar query (`SELECT COUNT(*) …`, `SELECT SUM("col") …`), reusing
the same WHERE translation as the rest of the query API. They are available both on the **query builder**
(when you hold a `DbConnection`) and on the **database context** (`I{Table}Set`).

## Query builder

Build a query with an optional `Where(...)`, then call a scalar terminal instead of `ExecuteAsync()`:

```csharp
await using var conn = factory.Create();

long active   = await User.Query(u => u.IsActive).WithConnection(conn).CountAsync();
decimal? rev  = await Order.Query(o => o.Paid).WithConnection(conn).SumAsync<decimal>(o => o.Total);
double?  avg  = await User.Query().WithConnection(conn).AvgAsync<double>(u => u.Age);
DateTime? max = await Login.Query(l => l.UserId == id).WithConnection(conn).MaxAsync<DateTime>(l => l.At);
Guid?    minId= await User.Query().WithConnection(conn).MinAsync<Guid>(u => u.Id);

// A single column's value from the first matching row:
string? email = await User.Query(u => u.Id == id).WithConnection(conn).ScalarAsync<string>(u => u.Email);
```

| Method | SQL | Returns |
|---|---|---|
| `CountAsync()` | `SELECT COUNT(*) …` | `long` |
| `SumAsync<T>(x => x.Col)` | `SELECT SUM("col") …` | `T?` |
| `AvgAsync<T>(x => x.Col)` | `SELECT AVG("col") …` | `T?` |
| `MinAsync<T>(x => x.Col)` | `SELECT MIN("col") …` | `T?` |
| `MaxAsync<T>(x => x.Col)` | `SELECT MAX("col") …` | `T?` |
| `ScalarAsync<T>(x => x.Col)` | `SELECT "col" …` (first row) | `T?` |

- The `Where(...)` predicate is translated and **parameterized** exactly like a normal query.
- `SUM`, `AVG`, `MIN`, and `MAX` return **`null` when no rows match** (the aggregate is SQL `NULL`), so the result type is `T?`.
- `SumAsync`, `AvgAsync`, `MinAsync`, and `MaxAsync` are constrained to value types (`where T : struct`), meaning numeric and date/time columns. For other shapes, use `ScalarAsync<T>` or a [`.sql` procedure](/database/0.3.2/advanced/procedure-mapping).
- The selector must be a **single mapped column** (`x => x.Amount`). Anything else throws `NotSupportedException`. An `[Encrypted]` column also cannot be aggregated, because encryption is non-deterministic.
- Every call is reported through the diagnostics pipeline as a `SELECT` (see [Diagnostics](/database/0.3.2/observability/diagnostics)).

## Database context

The same operations live on each `I{Table}Set`, take an optional predicate, and run inside the unit-of-work
scope (enlisting the ambient transaction automatically):

```csharp
long active = await db.ExecuteAsync(d => d.Users.CountAsync(u => u.IsActive));

await db.ExecuteTransactionAsync(async d =>
{
    decimal? total = await d.Orders.SumAsync<decimal>(o => o.Total, o => o.UserId == userId);
    DateTime? last = await d.Logins.MaxAsync<DateTime>(l => l.At, l => l.UserId == userId);
    // ...
});
```

`I{Table}Set` exposes `CountAsync(predicate?)`, `SumAsync<T>(selector, predicate?)`, `AvgAsync`, `MinAsync`,
`MaxAsync`, and `ScalarAsync<T>(selector, predicate?)`.

> **NOTE** `CountAsync` is a real `SELECT COUNT(*)`. It does **not** stream and count rows client-side.

## See also

- [WHERE expressions](/database/0.3.2/querying/reading/where) for the predicate syntax these reuse
- [Procedure mapping](/database/0.3.2/advanced/procedure-mapping) for arbitrary scalar SQL (`string` MIN/MAX, multi-column aggregates)