# Counts and aggregate reports (/docs/framework/guides/aggregates)



<SalesforceReference title="Aggregate functions and GROUP BY" href="https://developer.salesforce.com/docs/platform/salesforce-soql-sosl/guide/sforce-api-calls-soql-select-agg-functions.html" />

Generate `Opportunity` before trying these examples. Kysoql uses the org's
Describe metadata to decide which fields can be grouped or aggregated.

## Count records [#count-records]

Use bare `count()` when you only need a number:

```ts
import { db } from "./db";

const count = await db
  .selectFrom("Opportunity")
  .where("IsClosed", "=", false)
  .select(({ fn }) => fn.count())
  .execute();
```

Use `count(field)` or another aggregate when you want rows that can also be
grouped, filtered, or sorted:

```ts
import { db } from "./db";

const rows = await db
  .selectFrom("Opportunity")
  .select(({ fn }) => fn.count("Id").as("opportunityCount"))
  .execute();
```

Give row-producing aggregates an alias rather than relying on Salesforce names
such as `expr0`.

## Aggregate functions [#aggregate-functions]

```ts
import { db } from "./db";

const totals = db
  .selectFrom("Opportunity")
  .select(({ fn }) => [
    fn.count("Id").as("opportunityCount"),
    fn.countDistinct("AccountId").as("accountCount"),
    fn.sum("Amount").as("totalAmount"),
    fn.avg("Amount").as("averageAmount"),
    fn.min("CloseDate").as("firstCloseDate"),
    fn.max("CloseDate").as("lastCloseDate"),
  ])
  .where("IsClosed", "=", false);
```

| Function                               | Result                                              |
| -------------------------------------- | --------------------------------------------------- |
| `count(field)`, `countDistinct(field)` | `number`                                            |
| `sum(field)`, `avg(field)`             | `number \| null`                                    |
| `min(field)`, `max(field)`             | The field value, including `null` when applicable   |
| `format(aggregate)`                    | A localized string with the aggregate's nullability |

## Group results [#group-results]

Group before selecting ordinary fields:

```ts
import { db } from "./db";

const byStage = db
  .selectFrom("Opportunity")
  .select(({ fn }) => fn.sum("Amount").as("totalAmount"))
  .groupBy("StageName")
  .select("StageName")
  .orderBy("StageName");
```

You can also use grouping to return distinct grouped values without adding a
dummy aggregate:

```ts
import { db } from "./db";

const stages = db
  .selectFrom("Opportunity")
  .groupBy("StageName")
  .select("StageName")
  .orderBy("StageName");
```

This is SOQL grouping, not a general SQL `distinct()` method.

## Filter groups with HAVING [#filter-groups-with-having]

`WHERE` filters source records. `HAVING` filters the grouped result.

```ts
import { db } from "./db";

const significantStages = db
  .selectFrom("Opportunity")
  .select(({ fn }) => [
    fn.count("Id").as("opportunityCount"),
    fn.sum("Amount").as("totalAmount"),
  ])
  .where("IsClosed", "=", false)
  .groupBy("StageName")
  .select("StageName")
  .having((eb) =>
    eb.and([
      eb(eb.fn.count("Id"), ">", 5),
      eb(eb.fn.sum("Amount"), ">=", 100_000),
    ]),
  );
```

## Order by an aggregate [#order-by-an-aggregate]

Use the aggregate expression itself rather than its alias:

```ts
import { db } from "./db";

const ranked = db
  .selectFrom("Opportunity")
  .select(({ fn }) => fn.sum("Amount").as("totalAmount"))
  .groupBy("StageName")
  .select("StageName")
  .orderBy(({ fn }) => fn.sum("Amount"), "desc", "last")
  .orderBy("StageName");
```

## Rollups and cubes [#rollups-and-cubes]

```ts
import { db } from "./db";

const pipeline = db
  .selectFrom("Opportunity")
  .select(({ fn }) => fn.sum("Amount").as("totalAmount"))
  .groupByRollup(["StageName", "Type"])
  .select(["StageName", "Type"])
  .select(({ fn }) => [
    fn.grouping("StageName").as("stageSubtotal"),
    fn.grouping("Type").as("typeSubtotal"),
  ]);
```

Use `groupByCube()` for cube grouping. Rollup and cube accept up to three fields.
Their subtotal rows can make grouping fields nullable. `fn.grouping(field)`
returns `0 | 1`, where `1` marks a subtotal for that field.

<Accordions type="single">
  <Accordion title="Aggregate query rules worth knowing">
    * Ordinary selected fields must be part of the grouping set.
    * Ordinary grouping, rollup, and cube cannot be mixed in one query.
    * `HAVING` does not support semi-joins.
    * Root aggregate `OFFSET` is limited to `0..2000`.
    * Bare `count()` has a deliberately smaller builder surface than grouped aggregates.
    * Salesforce still decides whether a particular aggregate result can be paged.
  </Accordion>

  <Accordion title="Removing grouping">
    `clearGroupBy()` refuses to leave behind selections or clauses that still depend
    on grouping. When you need to turn a grouped report back into a record query,
    rebuilding the query is usually clearer.
  </Accordion>
</Accordions>

For calendar-style reports, continue with [date functions](/docs/framework/guides/date-functions).
For Apex output, see [Compile for Apex](/docs/core/apex/overview).