Aggregation, grouping and summarizing

Teo allows you to count records, aggregate number fields with or without grouping, and select distinct field values.

Count

Use count to count the number of records. The following example returns the number of artists in the database:

const { data: count } = await teo.artist.count()

2

Filtered count

count can be filtered and paginated.

const { data: count } = await teo.artist.count({
  where: {
    id: { lt: 50 }
  },
  take: 1
})

1

Aggregate

Teo allows you to aggregate on the number fields of a model. The following query returns the average age of all artists:

const { data: aggregations } = await teo.artist.aggregate({
  _avg: {
    age: true,
  },
})

{
  "_avg": {
    "age": 25
  }
}

Aggregate uses non null values to calculate avg and sum. If all values from records on a field are null, the aggregated value is also null.

Group by

Teo allows you to group aggregated values by some field values. The following example groups all artists by the name field and returns the average age for artists under each name:

const { data: groupByName } = await teo.artist.groupBy({
  by: ['name'],
  _avg: {
    age: true,
  },
})

[
  {
    "name": "Angela Peterson",
    "_avg": {
      "age": 23
    }
  },
  {
    "name": "Tony Justin",
    "_avg": {
      "age": 27
    }
  }
]

Filter group results with having

groupBy results can be filtered with having. having is different than where. where filters on records, while having filters on aggregation results.

const { data: groupByName } = await teo.artist.groupBy({
  by: ['name'],
  having: {
    age: {
      _avg: { gt: 25 }
    }
  }
  _avg: {
    age: true,
  },
})

[
  {
    "name": "Tony Justin",
    "_avg": {
      "age": 27
    }
  }
]

Distinct values

Teo allows you to remove duplicate records on some fields. distinct is often used with select to identify certain unique combinations of values in the rows of your table.

const { data: ages } = await teo.artist.findMany({
  distinct: ['age'],
  select: {
    age: true,
  }
})

[
  {
    "age": 23
  },
  {
    "age": 27
  }
]