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.Count(o => p) · g.Sum/Average/Min/Max(o => v)
  })
  [.Where(x => groupPredicate)]             // optional — filters the GROUPS (HAVING)
  [.OrderBy(x => col) | .OrderByDescending(x => col)]
  [.Take(n)]                                // a constant, or a count chosen at run time
  [.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.

When is it a database GROUP BY?#

When GroupBy is followed by a Select that reduces each group to one row of its key and its aggregates. That is the shape this page is about, and it is the one that never fetches the rows.

Anything else you do with a group — hold it, iterate it, take its first element, filter the groups by their size — needs the group's rows, so the rows the query selects are read and then grouped exactly as C#'s LINQ groups them. That is IGrouping<K, T>, and it gives C#'s answer; it is just not one row per group in the database.

Inside the reducing 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.Count(o => p), g.Sum(o => v), g.Average(o => v), g.Min(o => v), g.Max(o => v).

Any other column (g.First().Name, the group itself) needs the group's rows, which makes the read the row-reading form instead.

g.Count(o => p) counts the rows of the group the condition holds for — 0 for a group where it holds for none, so the group still appears. That is the difference from a Where before the grouping, which drops the rows first: a group none of whose rows pass is then not a group at all. One read, any number of conditional counts:

class WallStat { public string Wall; public int Routes; public int Hard; public int Open; }

entity Route {
  [Required, MaxLength(20)] string Wall;
  int Grade;
  bool Retired = false;
}

List<WallStat> PerWall() {
  return Route.GroupBy(r => r.Wall)
              .Select(g => new WallStat {
                Wall   = g.Key,
                Routes = g.Count(),
                Hard   = g.Count(r => r.Grade >= 7),     // COUNT(*) FILTER (WHERE …), in effect
                Open   = g.Count(r => !r.Retired) })
              .Where(w => w.Open > 0)                     // walls with at least one open route
              .ToList();
}

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#

One GROUP BY takes only Where before it. An OrderBy or a Skip/Take before the GroupBy is still ordinary LINQ and gives C#'s answer — the OrderBy sets the order the groups come out in (the order their keys first appear) and Take groups only the rows it takes — but it is answered over the rows read, as [[query-igrouping#stored-rows]] describes, not as one GROUP BY. To order the groups by a total, sort after the projection, which stays one statement.

After the projection, Take takes a constant (Take(10)) or a count chosen at run time (Take(n)) — either becomes the statement's LIMIT, and a negative count takes nothing, as C#'s Take does:

List<RegionTotal> TopRegions(int n) {
  return Order.GroupBy(o => o.Region)
              .Select(g => new RegionTotal { Region = g.Key, Orders = g.Count(), Total = g.Sum(o => o.Total) })
              .OrderByDescending(r => r.Total)
              .Take(n)
              .ToList();
}

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
• IGrouping<K, T> and ILookup<K, T> — the groups themselves (IGrouping<K, T>, ToLookup), when one row per group is not what you want
• class methods — the class a grouped projection targets