`Group`

The Group type describes a function that lets you specify that the values of a Cell in multiple ResultRows should be aggregated together.

(
  selectedCellId: Id,
  aggregate: "count" | "sum" | "avg" | "min" | "max" | Aggregate,
  aggregateAdd?: AggregateAdd,
  aggregateRemove?: AggregateRemove,
  aggregateReplace?: AggregateReplace,
): GroupedAs

	Type	Description
`selectedCellId`	`Id`	The `Id` of the `Cell` to aggregate. If the underlying `Cell` was selected 'as' a different `Id`, that should instead be used.
`aggregate`	`"count" \| "sum" \| "avg" \| "min" \| "max" \| Aggregate`	Either a string representing one of a set of common aggregation techniques ('count', 'sum', 'avg', 'min', or 'max'), or a function that aggregates `Cell` values from each `Row` to create the aggregate's overall.
`aggregateAdd?`	`AggregateAdd`	A function that can be used to optimize a custom `Aggregate` by providing a shortcut for when a single value is added to the input values - for example, when a `Row` is added to the `Table`.
`aggregateRemove?`	`AggregateRemove`	A function that can be used to optimize a custom `Aggregate` by providing a shortcut for when a single value is removed from the input values - for example, when a `Row` is removed from the `Table`.
`aggregateReplace?`	`AggregateReplace`	A function that can be used to optimize a custom `Aggregate` by providing a shortcut for when a single value in the input values is replaced with another - for example, when a `Row` is updated.
returns	`GroupedAs`

The Group function is provided to the third query parameter of the setQueryDefinition method. When called, it should refer to a Cell Id (or aliased Id) specified in one of the Select functions, and indicate how the values should be aggregated.

This is applied after any joins or where-based filtering.

If you provide a Group for every Select, the result will be a single Row with every Cell having been aggregated. If you provide a Group for only one, or some, of the Select clauses, the others will be automatically used as dimensional values (analogous to the 'group bysemantics in SQL), within which the aggregations ofGroup` Cells will be performed.

You can join the same underlying Cell multiple times, but in that case you will need to use the 'as' function to distinguish them from each other.

The second parameter can be one of five predefined aggregates - 'count', 'sum', 'avg', 'min', and 'max' - or a custom function that produces your own aggregation of an array of Cell values.

The final three parameters, aggregateAdd, aggregateRemove, aggregateReplace need only be provided when you are using your own custom aggregate function. These give you the opportunity to reduce your custom function's algorithmic complexity by providing shortcuts that can nudge an aggregation result when a single value is added, removed, or replaced in the input values.

Examples

This example shows a query that calculates the average of all the values in a single selected Cell from a joined Table.

import {createQueries, createStore} from 'tinybase';

const store = createStore()
  .setTable('pets', {
    fido: {species: 'dog'},
    felix: {species: 'cat'},
    cujo: {species: 'dog'},
    lowly: {species: 'worm'},
  })
  .setTable('species', {
    dog: {price: 5},
    cat: {price: 4},
    worm: {price: 1},
  });

const queries = createQueries(store);
queries.setQueryDefinition('query', 'pets', ({select, join, group}) => {
  select('species', 'price');
  // from pets
  join('species', 'species');
  group('price', 'avg').as('avgPrice');
});

console.log(queries.getResultTable('query'));
// -> {0: {avgPrice: 3.75}}
// 2 dogs at 5, 1 cat at 4, 1 worm at 1: a total of 15 for 4 pets

This example shows a query that calculates the average of a two Cell values, aggregated by the two other dimensional 'group by' Cells.

import {createQueries, createStore} from 'tinybase';

const store = createStore()
  .setTable('pets', {
    fido: {species: 'dog', owner: 'alice'},
    felix: {species: 'cat', owner: 'bob'},
    cujo: {species: 'dog', owner: 'bob'},
    lowly: {species: 'worm', owner: 'alice'},
    carnaby: {species: 'parrot', owner: 'bob'},
    polly: {species: 'parrot', owner: 'alice'},
  })
  .setTable('species', {
    dog: {price: 5, legs: 4},
    cat: {price: 4, legs: 4},
    parrot: {price: 3, legs: 2},
    worm: {price: 1, legs: 0},
  });

const queries = createQueries(store);
queries.setQueryDefinition('query', 'pets', ({select, join, group}) => {
  select('pets', 'owner'); //    group by
  select('species', 'price'); // grouped
  // from pets
  join('species', 'species');
  group(
    'price',
    (cells) => Math.min(...cells.filter((cell) => cell > 2)),
    (current, add) => (add > 2 ? Math.min(current, add) : current),
    (current, remove) => (remove == current ? undefined : current),
    (current, add, remove) =>
      remove == current
        ? undefined
        : add > 2
          ? Math.min(current, add)
          : current,
  ).as('lowestPriceOver2');
});

queries.forEachResultRow('query', (rowId) => {
  console.log({[rowId]: queries.getResultRow('query', rowId)});
});
// -> {0: {owner: 'alice', lowestPriceOver2: 3}}
// -> {1: {owner: 'bob', lowestPriceOver2: 3}}
// Both have a parrot at 3. Alice's worm at 1 is excluded from aggregation.

Since

v2.0.0