# GroupBy (and HAVING)

> Group rows by a key and reduce each group to one row — `g.Key`, `g.Count()`, `g.Count(o => p)`, `g.Sum/Average/Min/Max(…)` — computed by the database as a real GROUP BY. A `Where` BEFORE the grouping filters the rows that get grouped; a `Where` AFTER the projection filters the groups themselves, which is SQL's HAVING. You never write the word "having": which side of the `GroupBy` a `Where` sits on says what it filters.

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

## Summary        {#summary}
Group rows by a key and reduce each group to a single row, in the database:

```osy title="revenue per region, biggest first" test app=query-group-by
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      {#signature}
```osy syntax
<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    {#description}

### Does my `Where` filter rows, or groups?   {#two-wheres}
The position of a `Where` is what it means, and this is the whole grammar of grouping:

| Where it sits | What it filters | SQL |
|---|---|---|
| **before** `GroupBy` | the **rows** that get grouped | `WHERE` |
| **after** the `Select` | the **groups**, by their aggregates | `HAVING` |

*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?   {#projection}
**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>`](https://osysharp.com/reference/query/igrouping/), 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](https://osysharp.com/reference/query/igrouping/) 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:

```osy title="a count, and two counts of the rows a condition holds for, per wall" test app=query-group-by-count-where
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?   {#composite-keys}
Group by more than one column with an object key, and read the parts back off `g.Key`:

```osy title="a breakdown by two columns" test app=query-group-by
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`   {#target}
Project into a [`class`](https://osysharp.com/reference/class/methods/) 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   {#restrictions}
**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:

```osy title="the top n regions, n chosen by the caller" test app=query-group-by
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       {#examples}
Every aggregate at once, over a parent's children — a per-invoice summary computed entirely in the database:

```osy title="min, max, average and count, per group" test app=query-group-by
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       {#see-also}
- [Sum / Average / Min / Max / Count](https://osysharp.com/reference/query/aggregates/) — the same folds over a whole query rather than per group (and the null-on-empty rule)
- [Select (projections)](https://osysharp.com/reference/query/select/) — projections without grouping
- [OrderBy / ThenBy](https://osysharp.com/reference/query/ordering/) — sorting the groups by an aggregate
- [LINQ over a local list](https://osysharp.com/reference/query/in-memory-linq/) — `GroupBy` over a local `List<T>`, evaluated in memory
- [IGrouping<K, T> and ILookup<K, T>](https://osysharp.com/reference/query/igrouping/) — the groups themselves (`IGrouping<K, T>`, `ToLookup`), when one row per group is not what you want
- [class methods](https://osysharp.com/reference/class/methods/) — the `class` a grouped projection targets