---
title: Calculations
description: "Count, add up, average, and find the extremes of an attribute, overall or per group, without loading the records."
seo:
  description: "Count, sum, average, minimum, and maximum in Active Record without loading records, per group with group and having, and exact money sums on D1."
---

## At a glance

| To                             | Write                                                     | Returns                         |
| ------------------------------ | --------------------------------------------------------- | ------------------------------- |
| add up an attribute            | `sum("priceCents")`                                       | a number, 0 with no rows        |
| average it                     | `average("priceCents")`                                   | a number, or `null`             |
| find the smallest or largest   | `minimum("priceCents")`, `maximum("createdAt")`           | the attribute’s type, or `null` |
| calculate an expression        | ``average(sql`…`)``                                       | what the database returns       |
| calculate per group            | `group("isAvailable").count()`                            | a `Map` from group to result    |
| keep some groups               | `group("isAvailable").having(…)`, with an `sql` condition | a grouped Relation              |
| choose the calculation by name | `calculate("sum", "priceCents")`                          | as the calculation named        |

## The Model

The examples use this Model, whose attributes come from its table through the schema `active-record schema` generates:

```ts product.ts
import { ApplicationRecord } from "./application-record";

export class Product extends ApplicationRecord {}
```

```ts schema.ts
// Generated by `active-record schema` from migrations/ — don't edit.
// schema: 69a8287500f9

import { defineSchema, type TableAttributes } from "@forestfuture/active-record";
import { d1 } from "@forestfuture/active-record/d1";

/** products — last changed by 0001_create_products.sql */
export const products = {
  id: { type: "string", null: false, generateId: true },
  name: { type: "string", null: false },
  priceCents: { type: "integer", null: false },
  isAvailable: { type: "boolean", null: false },
  createdAt: { type: "datetime", null: false },
  updatedAt: { type: "datetime", null: false },
} as const;

/** The database's adapter and binding, its tables (with their primary keys), and the Models built on them. */
export const schema = defineSchema({
  adapter: d1({ binding: "DB" }),
  tables: {
    products: { attributes: products, primaryKey: "id" },
  },
  models: { Product: "products" },
});

declare module "../models/product" {
  interface Product extends TableAttributes<typeof products, (typeof Product)["attributes"]> {}
}
```

```sql D1
CREATE TABLE products (
  id TEXT PRIMARY KEY NOT NULL,
  name TEXT NOT NULL,
  price_cents INTEGER NOT NULL,
  is_available BOOLEAN NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL
);
```

```sql Postgres
CREATE TABLE products (
  id UUID PRIMARY KEY,
  name TEXT NOT NULL,
  price_cents INTEGER NOT NULL,
  is_available BOOLEAN NOT NULL,
  created_at TIMESTAMPTZ NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL
);
```

## Calculating values

Add up, average, or find the extremes of an attribute:

```ts
import { Product } from "./models/product";

const total = await Product.where({ isAvailable: true }).sum("priceCents"); // 0 with no products
const averagePrice = await Product.average("priceCents"); // a number, or null
const cheapest = await Product.minimum("priceCents"); // or null
const newest = await Product.maximum("createdAt"); // a Date, or null
```

- `sum` and `average` take number attributes: integers, reals, and decimals. A decimal’s sum is text, such as `"12.8"`.
- `minimum` and `maximum` take any attribute you can compare. The result has the attribute’s own type, so a datetime’s is a `Date`.
- NULLs are left out. With no rows, `sum` is 0 and the others are `null`. On `none()`, they answer without a query.

:::warning[Exact sums on D1]
D1 adds up a decimal column as floating-point numbers. A sum keeps about 15 digits, and small errors can build up over many rows. For an exact sum on D1, store the amount as whole cents in an integer column, as `priceCents` does, with a [custom type](/custom-types#defining-a-type) such as money if you like. D1 adds whole numbers exactly, and `sum` returns the custom type’s value.
:::

## Calculating over some rows

A calculation covers the rows `toArray()` would return, so a `limit`, an `offset`, or `distinct()` decides which values count:

```ts
import { Product } from "./models/product";

const mostExpensive = Product.order({ priceCents: "desc" }).limit(3);
const topThree = await mostExpensive.sum("priceCents"); // only those three
```

## Calculating an expression

Pass an `sql` fragment to calculate an expression:

```ts
import { column, sql } from "@forestfuture/active-record";
import { Product } from "./models/product";

const inDollars = sql`${column("priceCents")} / 100.0`;
const averageDollars = await Product.average(inDollars); // a number, or null
```

A fragment’s `sum` and `average` are numbers. Its `minimum` and `maximum` are the values the database returns.

:::note
`calculate("sum", "priceCents")` runs the calculation its first argument names. Use it when the name comes from elsewhere, such as a report’s settings.
:::

## Calculating per group

Group the rows by an attribute to calculate each group’s total:

```ts
import { Product } from "./models/product";

const byAvailability = await Product.group("isAvailable").count(); // Map { true => 3, false => 1 }
const inStock = byAvailability.get(true); // 3
const valueByAvailability = await Product.group("isAvailable").sum("priceCents");
```

- Each calculation returns a `Map` from the group’s value to its result. `count`, `sum`, `average`, `minimum`, and `maximum` all work this way.
- The keys have the attribute’s own type, so a boolean’s key is `true` or `false`, a datetime’s is a `Date`, and an enum’s is its name. Rows with a NULL value form a group with a `null` key.
- The groups come in the order the database returns them, unless you order them.

## Grouping by several values

Pass each attribute or expression to `group`:

```ts
import { column, sql } from "@forestfuture/active-record";
import { Product } from "./models/product";

const band = sql`CASE WHEN ${column("priceCents")} >= ${10_000} THEN 'pricey' ELSE 'cheap' END`;
const byBand = await Product.group("isAvailable", band).count(); // Map { [true, "cheap"] => 2, … }
```

Each key is then an array of the values, in the order you passed them. An expression’s value is whatever the database returns.

:::note
A `Map` finds an array or `Date` key only if it’s the same object, so `get([true, "cheap"])` finds nothing. Loop over the `Map` to read those groups.
:::

## Keeping some groups

Pass a condition on the groups’ totals to `having`:

```ts
import { sql } from "@forestfuture/active-record";
import { Product } from "./models/product";

const crowded = Product.group("isAvailable").having(sql`COUNT(*) > ${1}`);
const crowdedCounts = await crowded.count(); // Map { true => 3 }
```

## Ordering groups

Order the groups by a grouped attribute or by a total, and a `limit` then picks groups:

```ts
import { sql } from "@forestfuture/active-record";
import { Product } from "./models/product";

const largestFirst = Product.group("isAvailable").order(sql`COUNT(*) DESC`);
const largest = await largestFirst.limit(1).count(); // Map { true => 3 }
```

- `where`, `limit`, `offset`, and `distinct()` work before or after `group`. After `distinct()`, `count`, `sum`, and `average` take each group’s distinct values.
- You can only order by attributes you group by, or by an `sql` fragment. `group` drops an earlier order by any other attribute, such as one from a default scope. It keeps an earlier fragment, so call `reorder()` before `group` when a default scope orders by one. With a `limit` or `offset`, `group` throws instead of dropping an order, so call `reorder()` first there too.
- On `none()`, each calculation returns an empty `Map` without a query.

> **Compared with Rails**
>
> `average` always returns a number, where Rails returns a BigDecimal for integers and decimals. Rails puts a `limit` on the one row of its result, so `limit(3).sum(:price_cents)` adds up every row. Here, the limit picks the rows. Text attributes can’t be summed.
>
> Grouped results are a `Map`, not a Hash, so a key that’s an array or a time matches only by identity. A grouped Relation has no records, so there’s no `to_a` on it. Ordering groups by an attribute that isn’t grouped throws an error instead of reaching the database, and `group` drops such an order written before it, where Rails sends it and Postgres refuses it.
