Sum / Average / Min / Max / Count

Summary#

An aggregate folds a query into one value, in the database — the rows are never fetched:

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#

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

What does an aggregate answer when there are no rows?#

It depends on the aggregate, and the split is the same one C# makes:

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

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 ???#

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:

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#

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 if you need it back as another type.

An aggregate runs in the database, not in your loop#

An aggregate is one round trip that returns one value. Do not fetch rows to add them up yourself:

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?#

The same verbs work on a parent's children, correlated to that parent (Child collections (navigating a relation)):

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?#

Yes — the same verbs work over a List<T> you already hold, including a list of class 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.

class Weighing { public string Sku; public decimal Kg; }

[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 theSum` was not already giving you.

Examples#

The full set, and the two ways to treat an empty result:

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#

• GroupBy (and HAVING) — the same aggregates, once per group, with HAVING
• Where / Single / Count — Count / Any and the predicate they take
• Child collections (navigating a relation) — aggregating a parent's children
• Querying data — why an aggregate is one statement and not a loop