# GroupBy (and HAVING)

> Group rows by a key and reduce each group to one row — `g.Key`, `g.Count()`, `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.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    {#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.

### Why must `GroupBy` be followed by `Select`?   {#projection}
**`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?   {#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}
**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       {#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
- [class methods](https://osysharp.com/reference/class/methods/) — the `class` a grouped projection targets