# Joins and aggregates

> Group rows and compute totals, join tables on links or on computed keys, and count rows across links.

`select_rows` groups rows and computes totals with `group_by` and aggregates, and joins tables on links or on matching values. The examples query a diary of classic films with its cast and viewings.

## Group rows and compute totals

`group_by` groups the rows, and `aggregates` computes one value per group. These are the aggregates:

* **`count`**: the rows in the group
* **`count:col`**: the rows where the column has a value
* **`sum:col`**, **`avg:col`**, **`min:col`** and **`max:col`**: the total, average, smallest and largest value of the column

**`select_rows`**

```json
{
  "table": "film",
  "group_by": "status",
  "aggregates": {
    "films": "count",
    "rated": "count:rating",
    "minutes": "sum:minutes",
    "average": "avg:minutes",
    "longest": "max:minutes"
  }
}
```

Answer:

```json
{
  "table": "main.public.film",
  "total": 2,
  "rows": [
    {
      "status": "to watch",
      "films": 2,
      "rated": 0,
      "minutes": 228,
      "average": 114,
      "longest": 153
    },
    {
      "status": "watched",
      "films": 6,
      "rated": 6,
      "minutes": 535,
      "average": 89.166667,
      "longest": 113
    }
  ]
}
```

A link column makes one group per linked note. These are the viewings per film:

**`select_rows`**

```json
{ "table": "watch", "group_by": "film", "aggregates": { "viewings": "count" } }
```

Answer:

```json
{
  "table": "main.public.watch",
  "total": 6,
  "rows": [
    { "film": "[[Charade]]", "viewings": 1 },
    { "film": "[[His Girl Friday]]", "viewings": 1 },
    { "film": "[[Night of the Living Dead]]", "viewings": 1 },
    { "film": "[[Nosferatu]]", "viewings": 1 },
    { "film": "[[Sherlock Jr.]]", "viewings": 1 },
    { "film": "[[The Gold Rush]]", "viewings": 2 }
  ]
}
```

`date(col)` groups a date-time column by day. These are the viewings per day over the last week:

**`select_rows`**

```json
{
  "table": "watch",
  "group_by": "date(occurred_at)",
  "aggregates": { "viewings": "count" },
  "where": { "occurred_at": { "gte": "{today-7d}" } }
}
```

Answer:

```json
{
  "table": "main.public.watch",
  "total": 3,
  "rows": [
    { "date(occurred_at)": "2026-10-07", "viewings": 1 },
    { "date(occurred_at)": "2026-10-10", "viewings": 1 },
    { "date(occurred_at)": "2026-10-11", "viewings": 1 }
  ]
}
```

## Join rows on a computed key

`join` attaches to each row the rows of another table whose values match, and `on` maps a column of this table to a column of the other. `date(col)` matches a date-time by its day, optionally shifted by a number of days. Here each film watched in the last three days gets the viewing of its `watched_on` day. The viewing tells you where you saw the film and who came along.

**`select_rows`**

```json
{
  "table": "film",
  "columns": ["title", "watched_on"],
  "where": { "watched_on": { "gte": "{today-3d}" } },
  "join": {
    "table": "watch",
    "on": { "watched_on": "date(occurred_at)" },
    "as": "viewing",
    "type": "inner",
    "columns": ["where", "with"]
  }
}
```

Answer:

```json
{
  "table": "main.public.film",
  "total": 2,
  "rows": [
    {
      "path": "datanotes/main/public/film/Night of the Living Dead.md",
      "title": "Night of the Living Dead",
      "watched_on": "2026-10-11",
      "viewing": [
        {
          "path": "datanotes/main/public/watch/2026-10-11T2200.md",
          "where": "cinema",
          "with": "Oscar"
        }
      ]
    },
    {
      "path": "datanotes/main/public/film/The Gold Rush.md",
      "title": "The Gold Rush",
      "watched_on": "2026-10-10",
      "viewing": [
        {
          "path": "datanotes/main/public/watch/2026-10-10T2030.md",
          "where": "home",
          "with": "my sister"
        }
      ]
    }
  ]
}
```

## Join two tables on matching values

`join` attaches the rows of another table whose columns match. Here each film gets its viewings: the viewing’s `film` link matches the film’s own link, `file_link`.

**`select_rows`**

```json
{
  "table": "film",
  "columns": ["title"],
  "join": {
    "table": "watch",
    "on": { "file_link": "film" },
    "as": "viewings",
    "type": "left",
    "columns": ["where", "occurred_at"]
  }
}
```

Answer:

```json
{
  "table": "main.public.film",
  "total": 3,
  "rows": [
    {
      "path": "datanotes/main/public/film/Charade.md",
      "title": "Charade",
      "viewings": [
        {
          "path": "datanotes/main/public/watch/2026-09-26T2130.md",
          "where": "home",
          "occurred_at": "2026-09-26T21:30"
        }
      ]
    },
    {
      "path": "datanotes/main/public/film/His Girl Friday.md",
      "title": "His Girl Friday",
      "viewings": [
        {
          "path": "datanotes/main/public/watch/2026-03-14T2030.md",
          "where": "cinema",
          "occurred_at": "2026-03-14T20:30"
        }
      ]
    },
    {
      "path": "datanotes/main/public/film/Night of the Living Dead.md",
      "title": "Night of the Living Dead",
      "viewings": []
    }
  ]
}
```

## Count rows across a list of links

`group_by` on a list column counts each item once, so a film with two actors counts for both, and Cary Grant’s group holds two films. Night of the Living Dead has no cast here, so it falls in the `null` group.

**`select_rows`**

```json
{
  "table": "film",
  "group_by": "cast",
  "aggregates": { "films": "count", "minutes": "sum:minutes" }
}
```

Answer:

```json
{
  "table": "main.public.film",
  "total": 4,
  "rows": [
    { "cast": "[[Audrey Hepburn]]", "films": 1, "minutes": 113 },
    { "cast": "[[Cary Grant]]", "films": 2, "minutes": 205 },
    { "cast": "[[Rosalind Russell]]", "films": 1, "minutes": 92 },
    { "cast": null, "films": 1, "minutes": 96 }
  ]
}
```