# A grouping of groups — GroupBy, then GroupBy again, in one statement

> Group, then group the groups: each person's jobs per day, then per person how many days they worked and their busiest day. The database does both in one statement (the first grouping is a derived table the second groups), and only the final rows travel. It works over a `Join` too — answers joined to the jobs they are about, then grouped by team.

<!-- id: query-grouping-of-groups · area: query · stability: preview · html: https://osysharp.com/reference/query/grouping-of-groups/ -->

## Summary        {#summary}
Per person: on how many days they worked, and the most jobs they did on one day.

```osy title="days worked and the busiest day, per person" test app=query-grouping-of-groups
class PersonDay {
  public string Who;
  public DateTime Day;
  public int Jobs;
}

class PersonLoad {
  public string Who;
  public int Days;
  public int Busiest;
}

entity Job {
  [Required, MaxLength(60)] string Who;
  DateTime At;
}

List<PersonLoad> Loads() {
  var zone = TimeZoneInfo.FindSystemTimeZoneById("Europe/Stockholm");
  return Job.GroupBy(j => new() { Who = j.Who, Day = j.At.Bucket(TimeBucket.Day, zone) })
            .Select(g => new PersonDay { Who = g.Key.Who, Day = g.Key.Day, Jobs = g.Count() })
            .GroupBy(d => d.Who)
            .Select(g => new PersonLoad { Who = g.Key, Days = g.Count(), Busiest = g.Max(d => d.Jobs) })
            .OrderBy(p => p.Who)
            .ToList();
}
```

That is `SELECT … FROM (SELECT who, day, COUNT(*) … GROUP BY who, day) t GROUP BY who`: one statement, as many rows
as there are people.

## Signature      {#signature}
```osy syntax
Source.GroupBy(k1).Select(g => new Inner { … })      // the inner grouping, projected to a class
      .GroupBy(x => k2).Select(g => new Outer { … })  // grouped again: its element is the Inner row
// Source may be an entity, a Where over one, or a Join of two
```

## Description    {#description}

### Over a join   {#join}
A row that points at another by its id, not a reference, is joined and then grouped:

```osy syntax
Answer.Join(Job, a => a.About, j => j.Id, (a, j) => new { Team = j.Team, Good = a.Good ? 1 : 0 })
      .GroupBy(x => x.Team)
      .Select(g => new TeamAnswers { Team = g.Key, Answered = g.Count(), Good = g.Sum(x => x.Good) })
```

### As the reader may read   {#security}
The inner grouping reads the rows its reader may read, like any query: a mechanic who reads only their own jobs gets
their own days and nothing of anybody else's.

## See also       {#see-also}
- [GroupBy (and HAVING)](https://osysharp.com/reference/query/group-by/) — one grouping, and HAVING.
- [Join / LeftJoin / SelectMany](https://osysharp.com/reference/query/joins/) — joins on a value.
- [Days, weeks and months in a time zone — at.Bucket(TimeBucket.Month, zone)](https://osysharp.com/reference/query/time-buckets/) — the day in a time zone, as a key.