# Totals

> Return the counts and measures of every row that a search matches, next to its page, in SQL and on search engines.

A search page can return the totals of every row that its search matches, next to the rows of the page: a sum or an average over all the rows, and the number of rows per value of a field. A list screen shows them above its table, such as the invoices of a customer by status, with the amount of all of them.

The totals belong to the search: they count the rows of the same perimeter, extension restrictions, scopes, instructions, filters, text and `trashed` as the page, in one request.

## Declaring totals

A field opts in with `groupable()`, to count the rows per value, and `measurable()`, to measure its values:

```php [app/Rest/Resources/InvoiceResource.php]
use Lomkit\Rest\Fields\Enum;
use Lomkit\Rest\Fields\ID;
use Lomkit\Rest\Fields\Integer;

public function fields(): array
{
    return [
        ID::make('id'),
        Enum::make('status', InvoiceStatus::class)->groupable(),
        Integer::make('customer_id')->groupable(),
        Integer::make('amount_cents')->measurable(),
    ];
}
```

- `groupable()` takes an `Enum`, `Boolean`, `Text`, `Integer` or `ID` field with one value per row. `measurable()` takes an `Integer` or `Number` field.
- `isGroupable()` and `isMeasurable()` read them, and `FieldSet::groupable()` and `FieldSet::measurable()` list them.
- The definition of the resource refuses, with `InvalidFieldException`: a `groupable()` field of another type, a `measurable()` field of another type, either one on a `virtual()`, `resolveUsing()`, `inputOnly()` or `writeOnly()` field, `measurable()` with `selectable(false)`, and `groupable()` with `filterable(false)` or `filterUsing()`.
- A field that a [`NarrowsFields` extension](/extensions/authorization#narrowing-fields) hides or makes unfilterable for the user is neither groupable nor measurable for that user, and answers as an unknown field.

## The request

`search.totals` asks for `measures` over all the rows, and `groups`, each counting the rows per value of one field:

```json [POST /api/invoices/search]
{
  "search": {
    "filters": [{"field": "customer_id", "value": 42}],
    "totals": {
      "measures": [{"type": "sum", "field": "amount_cents"}, {"type": "avg", "field": "amount_cents"}],
      "groups": [{"field": "status"}]
    }
  }
}
```

- A measure has a `type`, `sum`, `avg`, `min` or `max`, and a `measurable()` field.
- A group has a `groupable()` field and an optional `limit`, the most values it returns, from 1 to `rest.limits.sizes.groups` (100), which is also its default.
- A search may send `measures` alone or `groups` alone, and needs at least one measure or group.

## The response

The page adds a root `totals` key next to `total`, only when the request asks for it:

```json [200 OK]
{
  "current_page": 1,
  "data": [...],
  "from": 1,
  "last_page": 3,
  "per_page": 50,
  "to": 50,
  "total": 128,
  "totals": {
    "exact": true,
    "all": {"count": 128, "sum_amount_cents": 1534200, "avg_amount_cents": 11985.9375},
    "groups": [
      {
        "field": "status",
        "buckets": [{"value": "paid", "count": 97}, {"value": "open", "count": 24}, {"value": "void", "count": 7}],
        "other": {"count": 0},
        "truncated": false
      }
    ]
  },
  "meta": {}
}
```

- `all.count` counts every row that the search matches. It is always returned, and is not a measure type. It equals `total`, except in two cases: on Turbopuffer, whose `total` stops at 10,000 while `all.count` counts every row (`total_capped`), and in SQL when a page holds every row of a search with `distinct()` and a join, where `total` counts the distinct rows of that page and the totals count the joined rows.
- Each measure is under `<type>_<field>`, such as `sum_amount_cents`.
- `groups` holds one entry per group asked for, in the order of the request, and `[]` without groups.
- `buckets` lists the values of the field with their `count`, by `count` descending, then by value. A bucket `value` has the type of its field: the backed value for an `Enum`.
- `other.count` counts the rows outside the buckets: the other values and the rows without a value. A row without a value never forms a bucket.
- `truncated` is `true` when at least one other value exists past the `limit`.
- The buckets plus `other.count` always add up to `all.count`.
- `exact` is always `true`: every figure counts every matched row. A backend that cannot count exactly refuses, with a `422` when the request asks for what it cannot compute, and with an exception when its answer reveals an inexact count.
- `meta` stays for the resource and the extensions.

### Measures

- `sum`, `min` and `max` of an `Integer` field are integers. Those of a `Number` field, and every `avg`, are floats. JSON keeps the fraction of a whole float, such as `150.0`.
- Over no row, `sum` is `0`, or `0.0` on a `Number` field, and `avg`, `min` and `max` are `null`. The measures ignore `null` values.
- A text without terms, such as `*`, matches no row and returns empty totals without a query. See [Text search](/search/text-search#a-text-without-terms).

### Tied values

When values tie at the `limit` of a group, the database or the engine chooses which tied value it returns. The buckets are then sorted again by count and value, so the order of tied values can differ from the database order while the counts and `other` stay right.

## Refusals

`search.totals` answers `422` for:

<table>
<thead>
  <tr>
    <th>
      Case
    </th>
    
    <th>
      Key
    </th>
    
    <th>
      Message
    </th>
  </tr>
</thead>

<tbody>
  <tr>
    <td>
      No measure and no group (<code>
        {}
      </code>
      
      )
    </td>
    
    <td>
      <code>
        search.totals
      </code>
    </td>
    
    <td>
      <code>
        The search.totals field must ask for measures or groups.
      </code>
    </td>
  </tr>
  
  <tr>
    <td>
      A field that is not groupable or measurable
    </td>
    
    <td>
      <code>
        search.totals.groups.0.field
      </code>
      
      , <code>
        search.totals.measures.0.field
      </code>
    </td>
    
    <td>
      <code>
        The selected search.totals.groups.0.field is invalid.
      </code>
    </td>
  </tr>
  
  <tr>
    <td>
      Another measure type, <code>
        count
      </code>
      
       included
    </td>
    
    <td>
      <code>
        search.totals.measures.0.type
      </code>
    </td>
    
    <td>
      <code>
        The selected search.totals.measures.0.type is invalid.
      </code>
    </td>
  </tr>
  
  <tr>
    <td>
      A repeated group or measure
    </td>
    
    <td>
      <code>
        search.totals.groups.1
      </code>
    </td>
    
    <td>
      <code>
        The search.totals.groups.1 field has a duplicate value.
      </code>
    </td>
  </tr>
  
  <tr>
    <td>
      A <code>
        limit
      </code>
      
       that is not an integer from 1 to <code>
        sizes.groups
      </code>
    </td>
    
    <td>
      <code>
        search.totals.groups.0.limit
      </code>
    </td>
    
    <td>
      <code>
        The search.totals.groups.0.limit field must not be greater than 100.
      </code>
    </td>
  </tr>
  
  <tr>
    <td>
      More groups than <code>
        sizes.totals_groups
      </code>
    </td>
    
    <td>
      <code>
        search.totals.groups
      </code>
    </td>
    
    <td>
      <code>
        The search.totals.groups field must not have more than 3 items.
      </code>
    </td>
  </tr>
  
  <tr>
    <td>
      A resource whose fields declare no total
    </td>
    
    <td>
      <code>
        search.totals
      </code>
    </td>
    
    <td>
      <code>
        The search.totals field is prohibited.
      </code>
    </td>
  </tr>
  
  <tr>
    <td>
      A resource that caps its count, with <code>
        $maxTotal
      </code>
      
       or a <code>
        NarrowsLimits
      </code>
      
       extension for the user
    </td>
    
    <td>
      <code>
        search.totals
      </code>
    </td>
    
    <td>
      <code>
        The search.totals field is prohibited: this resource caps the count of its rows.
      </code>
    </td>
  </tr>
  
  <tr>
    <td>
      A resource that overrides <code>
        paginate()
      </code>
    </td>
    
    <td>
      <code>
        search.totals
      </code>
    </td>
    
    <td>
      <code>
        The search.totals field is prohibited: this resource counts its pages itself.
      </code>
    </td>
  </tr>
</tbody>
</table>

- Counting every row would pass the cap of a resource that caps its count, and the package cannot count the pages of a `paginate()` override the same way.
- `totals` is refused in includes (`The search.includes.0.totals field is prohibited.`), in the search of an action or a metric, and in `Rest::search()->lazy()`.

## Outside HTTP

`Rest::search()->render()` returns the same root `totals`, with the PHP types above. `page()` throws `SearchRunException::totalsOnPage()` when the payload holds `totals`, since a paginator holds the rows only: call `render()`. See [Searching outside HTTP](/search/outside-http#page-and-render).

## In the details

`/details?with=fields.capabilities` adds two keys to each field:

```json
{"name": "amount_cents", "label": null, "type": "integer", "nullable": false, "select": true, "sort": true, "group": false, "measures": ["sum", "avg", "min", "max"], "create": true, "update": true}
```

- `group` is `true` when `search.totals.groups` accepts the field, and `measures` lists the measure types that `search.totals.measures` accepts on it.
- Both are `false` and `[]` when the resource refuses totals to the user: a capped count or a `paginate()` override.
- The pivot fields of a relation do not have them.

See [Details](/digging-deeper/details#fields). The OpenAPI document adds `search.totals` to the search of a resource that declares totals, as `{Resource}SearchTotals`, and an optional root `totals` to its page, as `{Resource}Totals`. See [OpenAPI](/digging-deeper/openapi#totals).

## How SQL counts

- The totals run one query for `all`, `count(*)` with the measures, and one query per group, `GROUP BY` the column with `limit + 1` rows, on the query that counts `total`.
- Each group counts its own rows with a window function, so its buckets and `other` always add up. It needs MySQL 8, MariaDB 10.2, PostgreSQL, or SQLite 3.25 or later.
- The page and the totals are separate queries: they agree when no write happens between them. Ask for totals when the filters change rather than on each page.
- The totals count the rows that `total` counts: a scope that joins counts each joined row, a scope that groups counts each group, and a `union()` counts the rows of the whole union.
- A grouped `Text` column follows its collation: a case-insensitive MySQL collation counts `Jazz` and `jazz` as one value.

## On a search engine

A search on a [search engine](/search/search-engines) computes totals too, with the same request and the same root `totals`, when its `ScoutEngine` declares them:

```php [app/Rest/Resources/InvoiceResource.php]
use Lomkit\Rest\Fields\Integer;
use Lomkit\Rest\Text\Engine\EngineQuery;
use Lomkit\Rest\Text\ScoutEngine;

return ScoutEngine::make()
    ->filterable(['status', 'customer_id', 'amount_cents'])
    ->groupable(['status', 'customer_id'])
    ->measurable(['amount_cents'])
    ->perimeter(fn (EngineQuery $query) => $query->whereIn('team_id', $this->request()->user()->teamIds()))
    ->indexAttributes([Integer::make('team_id')])
    ->servesLists();
```

- `groupable()` and `measurable()` take names of the `filterable()` list, never of `indexAttributes()`, as `sortable()` does. A grouped attribute is an `ID`, `Integer`, `Text`, `Boolean` or `Enum` attribute with one value per row, a measured one an `Integer` or `Number` attribute.
- The first search of the resource, `/details?with=text` and `rest:check` (under `rows`) refuse, with `InvalidTextSearchException`, a name outside `filterable()`, a type that the engine does not group or measure, a repeated name, and these declarations on an engine that computes no totals. Typesense cannot facet on the document `id`: grouping it throws `InvalidTextSearchException::ungroupableAttributeType()`.
- A resource whose engine computes totals but declares none prohibits the key: `The search.totals field is prohibited.`
- The totals come from the search that reads the page, with the same filters, text, perimeter, restrictions and trashed rows. They count the index, as `total` does, so a row counted in `dropped` is counted in the totals too. Curated rows count as the page lists them: nothing outside the filters is counted.
- The index must facet each grouped and measured attribute, as it must sort a `sortable()` one, and index each measured attribute as a JSON number.
- A field that a `NarrowsFields` extension hides or makes unfilterable leaves both lists for the user, and answers as an unknown attribute.

### What each engine computes

Every built-in engine counts the rows per value. The measures depend on the engine:

<table>
<thead>
  <tr>
    <th>
      Engine
    </th>
    
    <th>
      Measures
    </th>
    
    <th>
      Notes
    </th>
  </tr>
</thead>

<tbody>
  <tr>
    <td>
      Typesense
    </td>
    
    <td>
      <code>
        sum
      </code>
      
      , <code>
        avg
      </code>
      
      , <code>
        min
      </code>
      
      , <code>
        max
      </code>
    </td>
    
    <td>
      A measured <code>
        Number
      </code>
      
       attribute needs a <code>
        decimal:N
      </code>
      
       cast on the model. The schema of the index is read before each search with totals
    </td>
  </tr>
  
  <tr>
    <td>
      Meilisearch
    </td>
    
    <td>
      <code>
        min
      </code>
      
      , <code>
        max
      </code>
    </td>
    
    <td>
      A search with groups reads the faceting settings of the index once
    </td>
  </tr>
  
  <tr>
    <td>
      Algolia
    </td>
    
    <td>
      <code>
        sum
      </code>
      
      , <code>
        min
      </code>
      
      , <code>
        max
      </code>
    </td>
    
    <td>
      A group returns at most 999 values: a <code>
        limit
      </code>
      
       above 999 answers <code>
        422
      </code>
      
      . The faceting settings of the index are read at each search with totals
    </td>
  </tr>
  
  <tr>
    <td>
      Turbopuffer
    </td>
    
    <td>
      <code>
        sum
      </code>
      
      , <code>
        avg
      </code>
      
      , <code>
        min
      </code>
      
      , <code>
        max
      </code>
    </td>
    
    <td>
      Engine lists without text only. A group that reaches 9,999 values, the most that one query returns, throws <code>
        cappedFacetValues()
      </code>
      
      . <code>
        all.count
      </code>
      
       counts every matched row while <code>
        total
      </code>
      
       stops at 10,000
    </td>
  </tr>
  
  <tr>
    <td>
      <code>
        collection
      </code>
      
      , <code>
        database
      </code>
    </td>
    
    <td>
      <code>
        sum
      </code>
      
      , <code>
        avg
      </code>
      
      , <code>
        min
      </code>
      
      , <code>
        max
      </code>
    </td>
    
    <td>
      <code>
        database
      </code>
      
       computes the totals in SQL on the query that it builds, <code>
        collection
      </code>
      
       in PHP on the rows that it matched
    </td>
  </tr>
  
  <tr>
    <td>
      <code>
        null
      </code>
    </td>
    
    <td>
      <code>
        sum
      </code>
      
      , <code>
        avg
      </code>
      
      , <code>
        min
      </code>
      
      , <code>
        max
      </code>
    </td>
    
    <td>
      Empty totals
    </td>
  </tr>
</tbody>
</table>

- A measure that the engine does not compute on a field answers `422` under `search.totals.measures.N.type`: `The search.totals.measures.0.type field holds a measure that the search engine of this resource does not compute on this field.` `/details` leaves it out.
- On Turbopuffer, a text search with `totals` answers `422`: `The search.totals field is refused: the search engine of this resource computes totals for a list without text only.` Its totals come from aggregation queries apart from the page, with the same filters and a strong consistency, so they agree with the page when no write happens between them. These figures rely on the aggregations that the Turbopuffer documentation describes: they could not be checked against a live server.
- A profile that an extension provides computes no totals: `The search.totals field is refused: the search engine of this resource computes no totals.`

### Exact figures only

The package refuses rather than shows a figure that does not count every matched row. A search throws `Lomkit\Rest\Exceptions\EngineSearchException` when:

- the engine reports that it stopped early or sampled the facets (`partialFacets()`);
- the values of a group count more than the matched rows, since the attribute holds several values per row (`multiValuedFacet()`): a grouped attribute must hold one value per row;
- the engine cannot facet an attribute, or leaves one out of its facets (`missingFacet()`);
- a measured attribute is not a numeric facet in the schema of the index, or is indexed as a string (`unmeasurableFacet()`);
- the index keeps one hit per distinct value, such as `distinct` with `attributeForDistinct` on Algolia (`distinctFacets()`);
- the engine answers without the count of the matched rows (`missingCount()`, on Turbopuffer);
- the index caps the values that it returns for an attribute without sorting them by count (`cappedFacetValues()`): on Typesense, Meilisearch and Algolia, raise the cap of the index, or sort its values by count and keep `limit` below the cap; on Turbopuffer, the cap of 9,999 values is fixed;
- the package cannot read that cap or the schema of the index (`unreadFaceting()`, `unreadSchema()`).

### Options that totals own

A resource whose `ScoutEngine` declares totals owns the engine options that make its facets exact, on every search, with or without totals: `EngineQuery::options()` refuses them with `InvalidEngineQueryException`, and a model that returns one from its search parameters throws `InvalidTextSearchException` at the first search, which `rest:check` reports under `rows`.

<table>
<thead>
  <tr>
    <th>
      Engine
    </th>
    
    <th>
      Owned options
    </th>
  </tr>
</thead>

<tbody>
  <tr>
    <td>
      Typesense
    </td>
    
    <td>
      <code>
        facet_by
      </code>
      
      , <code>
        max_facet_values
      </code>
      
      , <code>
        facet_query
      </code>
      
      , <code>
        facet_strategy
      </code>
      
      , <code>
        facet_sample_percent
      </code>
      
      , <code>
        facet_sample_threshold
      </code>
      
      , <code>
        facet_return_parent
      </code>
      
      , <code>
        search_cutoff_ms
      </code>
      
      , <code>
        pinned_hits
      </code>
      
      , <code>
        hidden_hits
      </code>
    </td>
  </tr>
  
  <tr>
    <td>
      Meilisearch
    </td>
    
    <td>
      <code>
        facets
      </code>
      
      , <code>
        distinct
      </code>
    </td>
  </tr>
  
  <tr>
    <td>
      Algolia
    </td>
    
    <td>
      <code>
        facets
      </code>
      
      , <code>
        maxValuesPerFacet
      </code>
      
      , <code>
        facetingAfterDistinct
      </code>
      
      , <code>
        sortFacetValuesBy
      </code>
      
      , <code>
        facetFilters
      </code>
      
      , <code>
        distinct
      </code>
    </td>
  </tr>
  
  <tr>
    <td>
      Turbopuffer
    </td>
    
    <td>
      <code>
        aggregate_by
      </code>
      
      , <code>
        group_by
      </code>
      
      , <code>
        consistency
      </code>
    </td>
  </tr>
</tbody>
</table>

### Decimal measures

An engine may store decimal numbers with limited precision. The package rounds the measures of a `Number` attribute, `avg` included, to the scale of its `decimal:N` cast on the model, on Typesense, Meilisearch, Algolia, Turbopuffer and the `collection` driver, when the attribute has that cast. The `database` driver computes its measures in SQL and does not round them.

```php [app/Rest/Resources/InvoiceResource.php]
Number::make('amount')->measurable(),
```

```php [app/Models/Invoice.php]
protected function casts(): array
{
    return ['amount' => 'decimal:2'];
}
```

- Typesense stores decimals with limited precision, so it requires the cast: `measurable()` on a `Number` attribute without it throws `InvalidTextSearchException::unscaledMeasure()` at the first search, and `rest:check` reports it under `rows`.
- There, a single value above about 167,772 with cents is no longer exact to the cent: index money as integer cents in an `Integer` attribute, such as `amount_cents`, whose measures stay exact.

### One value per row

A grouped attribute holds one value per row. On an engine with a typed index schema, a list type throws `multiValuedFacet()`; elsewhere, keep it to one value yourself. A search engine may also normalize text values when it groups them, as a database collation does: values that differ in letter case can count as one bucket, shown in one of their forms.

### Totals in the details

`/details?with=text` adds `totals` to the `engine` block of a resource whose engine declares them, for the user of the request:

```json
{"totals": {"groups": ["status", "customer_id"], "measures": {"amount_cents": ["sum", "avg", "min", "max"]}, "lists_only": false}}
```

`measures` lists the measure types that the engine computes on each attribute, and `lists_only` is `true` when the engine computes totals for engine lists only, such as Turbopuffer.

## Grouping on two values

A group counts the rows per value of one field. To count them per pair of values, such as a status per month, store the pair as one value: a column that the database computes, declared as a `Text` field with `groupable()`, or an attribute that `toSearchableArray()` builds and the index facets.

```php [app/Models/Invoice.php]
public function toSearchableArray(): array
{
    return [
        // ...
        'status_month' => $this->status->value.'|'.$this->issued_on->format('Y-m'),
    ];
}
```

On a search engine, declare it in `filterable()` and `groupable()`:

```php [app/Rest/Resources/InvoiceResource.php]
use Lomkit\Rest\Fields\Text;

return ScoutEngine::make()
    ->filterable(['status', 'customer_id', 'amount_cents', Text::make('status_month')])
    ->groupable(['status', 'customer_id', 'status_month'])
    // ...
```

The buckets then read `paid|2026-03`, and the client splits each value.

## Limits

<table>
<thead>
  <tr>
    <th>
      Key
    </th>
    
    <th>
      Default
    </th>
    
    <th>
      Bounds
    </th>
  </tr>
</thead>

<tbody>
  <tr>
    <td>
      <code>
        rest.limits.sizes.measures
      </code>
    </td>
    
    <td>
      <code>
        20
      </code>
    </td>
    
    <td>
      The measures of one search
    </td>
  </tr>
  
  <tr>
    <td>
      <code>
        rest.limits.sizes.totals_groups
      </code>
    </td>
    
    <td>
      <code>
        3
      </code>
    </td>
    
    <td>
      The groups of one search
    </td>
  </tr>
  
  <tr>
    <td>
      <code>
        rest.limits.sizes.groups
      </code>
    </td>
    
    <td>
      <code>
        100
      </code>
    </td>
    
    <td>
      The <code>
        limit
      </code>
      
       of each group, and its default
    </td>
  </tr>
</tbody>
</table>

Their entries also count in `rest.limits.totals.entries`. A published config gets the new keys from the package config, which is merged at every level. See [Configuration](/getting-started/configuration#limits).
