# Sum / Average / Min / Max / Count

> Fold rows down to a single number — a query or a `List<T>` you already hold. The one thing to know before you use them: **Min/Max/Average answer null over no rows** — they have no zero identity, so those you must answer for (`Max(…) ?? 0`). **Sum and Count have one**: Sum over no rows is 0, exactly as `Enumerable.Sum()` is in C#, so you take it straight as a decimal and no `?? 0m` is needed, and Count is honestly 0 with Any false. Average is always decimal. A query folds in the database and a list folds in memory, with the same answers either way.

<!-- id: query-aggregates · area: query · stability: stable · html: https://osysharp.com/reference/query/aggregates/ -->

## Summary        {#summary}
An aggregate folds a query into one value, **in the database** — the rows are never fetched:

```osy title="one number, computed by the database" test app=query-aggregates
entity Order {
  [Required, MaxLength(40)] string Code;
  decimal Total;
  bool Cancelled;
  DateTime PlacedAt;
}

decimal Revenue() {
  return Order.Where(o => !o.Cancelled).Sum(o => o.Total);   // ← a plain decimal. No rows is 0, as in C#.
}

int OpenCount() {
  return Order.Count(o => !o.Cancelled);
}
```

## Signature      {#signature}
```osy syntax
<Query>.Count()                    // int  — how many rows
<Query>.Count(o => predicate)      // int  — how many match
<Query>.Any()  /  .Any(o => p)     // bool — is there at least one

<Query>.Sum(o => value)            // the selector's type — 0 over no rows, so take it as-is
<Query>.Average(o => value)        // decimal, NULLABLE   (always decimal)
<Query>.Min(o => value)            // the selector's type, NULLABLE
<Query>.Max(o => value)            // the selector's type, NULLABLE
```

`Sum` and `Average` need a **numeric** selector. `Min`/`Max` work on any scalar (dates and strings included). All four
require the selector — there is no argument-less `Sum()`.

## Description    {#description}

### What does an aggregate answer when there are no rows?   {#null-on-empty}
It depends on the aggregate, and the split is the same one C# makes:

| aggregate | over no rows | why |
|---|---|---|
| `Sum` | **0** | `Enumerable.Sum()` over an empty sequence is 0. "We spent nothing under Outreach" *is* zero, and a report printing nothing there is wrong in the one direction a reader cannot see. |
| `Count` | **0** | "how many" is honestly none. |
| `Any` | **false** | |
| `Min` · `Max` · `Average` | **null** | they have no zero identity. An empty catalogue has no cheapest price, and "from £0" is a lie — C# throws rather than invent one, and here you get null so you can say what to show. |

So `Sum` needs no `??` and never did — take it straight:

```osy title="Sum takes no ceremony; Min does" test app=query-aggregates
decimal SpendFor(Order o) {
  return Order.Where(x => x.Code == o.Code).Sum(x => x.Total);   // plain decimal — no rows is 0m
}

decimal? CheapestOpen() {
  return Order.Where(o => !o.Cancelled).Min(o => o.Total);       // stays NULLABLE — no orders has no cheapest
}
```

⚠ **This is one rule with three implementations, and they agree deliberately** — the SQL the database runs, the
server's own evaluator, and the client's. SQL's bare `SUM` over no rows *is* NULL, so the platform restores the zero
identity rather than letting the same expression answer differently depending on where it ran.

⚑ If the difference between *"totals zero"* and *"there is nothing here"* genuinely matters to you — a refund path,
say — ask that question directly with `Any()` or `Count()`, which is what it actually is. Do not try to read it out
of a `Sum`.

### I already tested that it is not empty — do I still need the `??`?   {#guarded}
**No.** A `Min`/`Max`/`Average` is null for exactly one reason — no rows — so a branch that runs only when the
source HAS rows is not nullable, and the compiler reads it that way. Both spellings of the local below are accepted,
and the value goes straight into a non-nullable field:

```osy title="the guard is a guard" test app=query-aggregates-guarded
entity Job { [Required, MaxLength(60)] string Title; int SortOrder; }

void AddToTheEnd(string title) {
  var next = Job.Any() ? Job.Max(j => j.SortOrder) + 1 : 1;      // `int`, not `int?`
  new Job { Title = title, SortOrder = next };
}

void AddToTheEndTheOtherWay(string title) {
  int next = Job.Count() == 0 ? 1 : Job.Max(j => j.SortOrder) + 1;   // the same, guarded the other way round
  new Job { Title = title, SortOrder = next };
}
```

`Any()`, `Any(p)`, `Count()`, `Count` and `Length` all read as the emptiness test, in either polarity and under a
`!`. What matters is that the guard tests **the same source the aggregate reads**.

⚠ **A `Where(…)` in between breaks it, and that is not a limitation — it is the truth.** `jobs.Any() ?
jobs.Where(j => j.Done).Max(j => j.Order) : 0` is still refused, because a filter can empty a collection that had
rows. Test what the aggregate actually reads, or answer for absence with `?? 0`.

⚠ **This is the only narrowing the language does.** It is one syntactic shape decided inside one conditional
expression — not general `if (x != null)` flow analysis, which Osy# does not have. A null test in a preceding
statement does not narrow anything.

### `Average` is always decimal   {#average}
Whatever you select, `Average` gives you a `decimal` (nullable). Averaging `int` quantities gives `2.5m`, not `2` —
which is what you meant, and what SQL does. Use [`Convert`](https://osysharp.com/reference/function/convert/) if you need it back as another type.

### An aggregate runs in the database, not in your loop   {#in-the-database}
An aggregate is one round trip that returns one value. Do not fetch rows to add them up yourself:

```osy title="the difference is the whole table" test app=query-aggregates
decimal Wrong() {
  decimal sum = 0m;
  foreach (var o in Order.ToList()) { sum = sum + o.Total; }   // ← fetches EVERY order to add them up
  return sum;
}

decimal Right() {
  return Order.Sum(o => o.Total);                              // ← the database adds them up; one number comes back
}
```

Both give the same answer on ten rows. On ten million, one of them is a `SELECT SUM(total)` and the other is an
outage.

### Can I aggregate a parent's children?   {#collections}
The same verbs work on a parent's children, correlated to that parent ([Child collections (navigating a relation)](https://osysharp.com/reference/query/collections/)):

```osy title="aggregate a parent's children" test app=query-aggregates
entity Invoice {
  [Required, MaxLength(40)] string Number;
  [ForeignKey(Invoice)] InvoiceLine[] Lines;
}

entity InvoiceLine {
  [Required] Invoice Invoice;
  [MaxLength(60)] string Sku;
  int Qty;
  decimal Amount;
}

decimal InvoiceTotal(Invoice inv) {
  return inv.Lines.Sum(l => l.Amount);            // one SQL statement, correlated to this invoice
}

bool HasBackorder(Invoice inv) {
  return inv.Lines.Any(l => l.Qty == 0);
}
```

### Does `Sum` work on a plain list, not just a query?   {#in-memory}
Yes — the same verbs work over a `List<T>` you already hold, including a list of [class](https://osysharp.com/reference/class/index/) values that
never came from the database. There is **no second dialect and no different answer**: an in-memory `Sum` is a plain
`decimal` and an empty list is `0m`, exactly as the query form is.

```osy title="the same Sum over a list you built yourself" test app=query-aggregates
class Weighing { public string Sku; public decimal Kg; }
```

```osy title="a list of class values totals the same way a query does" run app=query-aggregates
[Test]
void Summing_A_Plain_List() {
  var load = new List<Weighing>();
  load.Add(new Weighing { Sku = "A", Kg = 2m });
  load.Add(new Weighing { Sku = "B", Kg = 3m });

  decimal total = load.Sum(w => w.Kg);          // a plain decimal — no `??`, no nullable
  Assert.Equal(5m, total);

  decimal none = new List<Weighing>().Sum(w => w.Kg);
  Assert.Equal(0m, none);                       // an empty list is 0m, same as an empty query
}
```

⛔ So do **not** hand-roll an accumulator loop because you expect nullable trouble. `foreach (var w in load) { t = t
+ w.Kg; }` is longer, and it is not buying you anything the `Sum` was not already giving you.

## Examples       {#examples}
The full set, and the two ways to treat an empty result:

```osy title="every aggregate, and what empty means for each" test app=query-aggregates
class PriceBand { public decimal? From; public decimal? To; }

decimal AverageOrderValue() {
  return Order.Average(o => o.Total) ?? 0m;        // no orders → an average of nothing → call it 0
}

PriceBand Band() {
  return new PriceBand {
    From = Order.Min(o => o.Total),                // KEEP the null: an empty catalogue has no lowest price,
    To   = Order.Max(o => o.Total),                // and "from 0" would be a lie
  };
}

DateTime? LastOrderAt() {
  return Order.Max(o => o.PlacedAt);               // Min/Max work on dates and strings too, not just numbers
}
```

## See also       {#see-also}
- [GroupBy (and HAVING)](https://osysharp.com/reference/query/group-by/) — the same aggregates, once per group, with `HAVING`
- [Where / Single / Count](https://osysharp.com/reference/query/where/) — `Count` / `Any` and the predicate they take
- [Child collections (navigating a relation)](https://osysharp.com/reference/query/collections/) — aggregating a parent's children
- [Querying data](https://osysharp.com/reference/query/index/) — why an aggregate is one statement and not a loop