GroupBy (and HAVING)

Summary#

Group rows by a key and reduce each group to a single row, in the database:

class RegionTotal {
  public string Region;
  public int Orders;
  public decimal Total;
}

entity Order {
  [Required, MaxLength(60)] string Region;
  decimal Total;
  bool Cancelled;
  int Month;
}

List<RegionTotal> Revenue() {
  return Order.Where(o => !o.Cancelled)                 // filters the ROWS, before grouping
              .GroupBy(o => o.Region)
              .Select(g => new RegionTotal {
                Region = g.Key,
                Orders = g.Count(),
                Total  = g.Sum(o => o.Total) })
              .Where(r => r.Total > 1000m)              // filters the GROUPS — this is HAVING
              .OrderByDescending(r => r.Total)
              .Take(10)
              .ToList();
}

That is one SELECT … GROUP BY region HAVING SUM(total) > 1000 ORDER BY … LIMIT 10. No rows travel.

Signature#

<Entity>
  [.Where(o => rowPredicate)]              // optional — filters the ROWS that get grouped
  .GroupBy(o => key)                        // one key, or a composite: o => new() { A = o.X, B = o.Y }
  .Select(g => new T {                      // REQUIRED, and must come next
      Col = g.Key,                          //   the key (or g.Key.A for a composite)
      Agg = g.Sum(o => v),                  //   g.Count() · g.Sum/Average/Min/Max(o => v)
  })
  [.Where(x => groupPredicate)]             // optional — filters the GROUPS (HAVING)
  [.OrderBy(x => col) | .OrderByDescending(x => col)]
  [.Take(<constant int>)]
  [.ToList()]

Description#

Does my Where filter rows, or groups?#

The position of a Where is what it means, and this is the whole grammar of grouping:

Exclude cancelled orders from the totals is a row filter. Only show regions that made over £1000 is a group filter — and it cannot be a row filter, because no single row knows its group's total. You never type the word having; you put the Where on the side that says what you mean.

Why must GroupBy be followed by Select?#

GroupBy must be followed immediately by Select. There is no IGrouping value you can hold onto, hand around or iterate — a group only exists as the row it reduces to. That is a deliberate limit: a group you could carry around would be a promise to fetch its members later, which is the fetch this whole verb exists to avoid.

Inside that Select, a column is exactly one of two things:

• the key — g.Key (or g.Key.<Part> for a composite key), or
• an aggregate — g.Count(), g.Sum(o => v), g.Average(o => v), g.Min(o => v), g.Max(o => v).

Anything else is a compile error, because anything else would need a row that the group does not have.

g.Count() takes no predicate. g.Count(o => p) is not supported; filter before the group, or sum a condition.

How do I group by more than one column?#

Group by more than one column with an object key, and read the parts back off g.Key:

class MonthlyRegion {
  public string Region;
  public int Month;
  public decimal Total;
}

List<MonthlyRegion> ByRegionAndMonth() {
  return Order.GroupBy(o => new() { Region = o.Region, Month = o.Month })
              .Select(g => new MonthlyRegion {
                Region = g.Key.Region,          // ← per PART, not a bare g.Key
                Month  = g.Key.Month,
                Total  = g.Sum(o => o.Total) })
              .OrderBy(r => r.Region)
              .ToList();
}

A bare g.Key on a composite key is a compile error — there is no tuple type to hand you, and each part is a column in its own right.

The projection target is a class#

Project into a class you declared (a plain data shape). That is what gives the result a real type you can return, index and iterate — List<RegionTotal> above. An anonymous new { … } also works, but it has no name, so it cannot cross a function boundary; use it only where the result is consumed on the spot.

What may NOT precede the grouping#

Only Where. No OrderBy, no Skip/Take, no Include, no Distinct before a GroupBy — and the compiler says so rather than quietly ignoring them. Sorting rows that are about to be collapsed into groups would mean nothing; sort the groups after the projection, which is exactly what is supported. Paging the rows before grouping would compute your totals from an arbitrary slice of the table, which is a bug rather than a feature.

After the projection, Take needs a compile-time constant (Take(10), not Take(n)) — the top-N of a grouped report is a fixed shape, not a runtime page.

Examples#

Every aggregate at once, over a parent's children — a per-invoice summary computed entirely in the database:

class SkuStat {
  public string Sku;
  public int Times;
  public int TotalQty;
  public decimal Cheapest;
  public decimal Dearest;
  public decimal Average;
}

entity Line {
  [Required, MaxLength(60)] string Sku;
  int Qty;
  decimal Price;
}

List<SkuStat> PerSku() {
  return Line.GroupBy(l => l.Sku)
             .Select(g => new SkuStat {
               Sku      = g.Key,
               Times    = g.Count(),
               TotalQty = g.Sum(l => l.Qty),
               Cheapest = g.Min(l => l.Price),
               Dearest  = g.Max(l => l.Price),
               Average  = g.Average(l => l.Price) })
             .Where(s => s.Times > 1)              // HAVING COUNT(*) > 1 — SKUs sold more than once
             .OrderByDescending(s => s.TotalQty)
             .ToList();
}

See also#

• Sum / Average / Min / Max / Count — the same folds over a whole query rather than per group (and the null-on-empty rule)
• Select (projections) — projections without grouping
• OrderBy / ThenBy — sorting the groups by an aggregate
• LINQ over a local list — GroupBy over a local List<T>, evaluated in memory
• class methods — the class a grouped projection targets